Having issues calculating a percentage

Dan_Rosen · 2025-02-13T21:50:05+00:00

There was an error rendering this rich post.

I have two measures, [Delivery Date: Sum: Releases Delivered] and [Delivery Date: Sum: Releases Delivered On Time]. Each gets set to a value of 1 or 0 depending on the order release status. I'm trying to do a basic on time delivery calculation, but it keeps returning the number of releases delivered instead of the percentage. Any idea how I can resolve this?

I've been trying variations of this:

IIF(IFNULL([Delivery Date: Sum: Releases Delivered], 0) = 0,
0,
IFNULL([Delivery Date: Sum: Releases Delivered On Time], 0) / [Delivery Date: Sum: Releases Delivered]
)

Find more posts tagged with

Accepted answers

Enrico Menotti

Please try disabling it at the Space level and play with it at the Report level. Or copy your BQuery from the report and run it from Query Admin with either SELECT or pushdown:SELECT at the beginning.

If this makes a difference, this is my explanation: with pushdown on, your IF's and the ratio are calculated by the db, before any summation is carried on over the Measure. Basically you're defining a custom Measure. By default, the result is aggregated by summing up.

In general, you can do something like

SUM([Delivery Date: Releases Delivered On Time]/[Delivery Date: Releases Delivered])

and try pushdown on/off. If I have understood all right, with pushdown on you have first the ratio and then the sum, with pushdown off you have the sum of the two measures (note that the SUM() operator will act on both of them separately) and then the ratio.

There is a quite hidden documentation in the Classic docs about this.

https://docs.infor.com/birst/2024.x/en-us/birstdataandmodels/Pronto/Modeling_Reference/Pre-Aggregate_Report_Expressions.html?Highlight=custom%20expression

Note that in the docs they keep [Delivery Date: Sum: Releases Delivered On Time]. If you have pushdown enabled, this Sum: is ignored and can be omitted (provided there is an explicit aggregation operator SUM()). If pushdown is off, you can still omit this Sum: and the aggregation rule is deduced from the aggregation operator. If you have pushdown off and both aggregation rule and operator, I don't remember - please try. 😉

By the way, pushdown at the Space level overrides pushdown at Report and BQuery level - everything is always pushed down (if I'm right). I always disable pushdown at Space level.

Enrico

All comments

Enrico Menotti

Do you have pushdown enabled, either at the Report or at the Space level?

Dan_Rosen

@Enrico Menotti

I do have it enabled at the space level (push down to database in the space properties). would that cause an issue here? what impacts would changing that have across the board?

Diane McElhiney

Please refer to this documentation page Pre-Aggregate Report Expressions

Dan_Rosen

I see only the following:

Diane McElhiney

Please try the link again.

Enrico Menotti

In general, you can do something like

SUM([Delivery Date: Releases Delivered On Time]/[Delivery Date: Releases Delivered])

There is a quite hidden documentation in the Classic docs about this.

https://docs.infor.com/birst/2024.x/en-us/birstdataandmodels/Pronto/Modeling_Reference/Pre-Aggregate_Report_Expressions.html?Highlight=custom%20expression

By the way, pushdown at the Space level overrides pushdown at Report and BQuery level - everything is always pushed down (if I'm right). I always disable pushdown at Space level.

Enrico

Dan_Rosen

Thanks Enrico, I will play with this a bit. I see I have this option turned off in my previously made spaces, so likely I ran into this a while back and forgot about the setting when creating my new spaces recently. I'm used to expecting the behavior you described when it is disabled.