Positional Calculation Help

Good Morning,

I need some guidance on a positional calculation. I am trying to get the percentage of the total MRR's by QC Reason Code. The tools on Infor's website are not very clear on how the positional calculation works. It doesn't really provide context as to how the [Products.Total] field works and where it comes from.

Here is what my report expression looks like and it's not working.

([MRR Create Date: # Distinct MRR Number] /
[MRR Create Date: # Distinct MRR Number]{[SML QC Reason.QC Reason]}) * 100

thank you,

Find more posts tagged with

Birst - General Discussions

Comments

jgordos

Phil,

I think you're trying to get something like:

OFFSET	10	7%
EMPLOYEE	5	3%
etc..	x	y%
	142	100%

I'm going to use STAT() instead of a positional expression, because we want the denominator to be at the 'overall' grain. Positional calculations and Dimensional Expressions have an implied "WHERE". I don't see any other dimensions in your report to do the WHERE by, so I'm using STAT().

In any case, something like this should work:

([MRR Create Date: # Distinct MRR Number] /Stat(Sum, 0, SELECT [MRR Create Date: # Distinct MRR Number] FROM [All] )

Then you can apply the '%' style to the result to have it do the multiplication by 100 and adding the percent sign.

This Stat() will return the total OVERALL in the database. You may need to constrain it by [Time]

It's often the case, though, that STAT() can be a bit slow, so be aware of this.

Hope this helps,

-john

Enrico Menotti

I suggest that you post in the new Community @ developer.infor.com.

That said, if you do {[SML QC Reason.Total]} then Birst will issue a separate query to the db layer, where all columns belonging to dimension SML QC Reason are removed. So this would retrieve the measure aggregated at a higher level of grain, specifically without slicing by [SML QC Reason.QC Reason].

Let me rephrase again: if you calculate

[MRR Create Date: # Distinct MRR Number] / [MRR Create Date: # Distinct MRR Number]{[SML QC Reason.Total]}

and your report is as in your screenshot, then Birst will query the db 2 times:

SELECT [SML QC Reason.QC Reason], [MRR Create Date: # Distinct MRR Number] FROM [All]

SELECT [MRR Create Date: # Distinct MRR Number] FROM [All]

and calculate the ratio between the two results (row by row: the first query yields multiple values, the second only one). You can see this from the query logs.

So if you want to calculate the share with respect to the total count over the whole SML QC Reason dimension, this should give you the right result.

Copyright © 2025 Infor. All rights reserved.