If Statement and Set analysis in Pivot Table: excluding a Dimension leads to a wrong calculation.

[RP8]

Dear Experts!

I've been struggling for two days without results, therefore I ask support to the community.

I have a Pivot Table where the Measure is calculated based on a SET ANALYSIS

 On Top of it, the measure should be calculated multipling by -1  based on the VALUE of a DIMENSION.

The expression of the Measure looks similar to this (I'll over-simplify the whole expression, this is not the actual code)

=IF(DIMENSION  = 'VALUE'), -1, 1) * (SET ANALYSIS)

Everything works fine if I add the DIMENSION as first dimension of my Pivot Table.

The problem is I need to hide (better totally exclude) the DIMENSION in the Pivot. In this case the "if" statement above  fails, and the results have all positive sign.

Below the Details:

=IF(AT = 'RE'), -1, 1) * Sum({<SA = {'PL'}>*<LL = {'II7'}>*<T= {'[NONE]'}>*<AT -= {'AS'}>*<AT -= {'LI'}>} AMOUNT)

Any idea how to fix this?

Thank you in advance for your time and help!

RP

[GaryGiles]

You need to use the Aggr() function.  Something like this:

sum(aggr(if(AT='RE',-1,1)*sum({<SA = {'PL'}>*<LL = {'II7'}>*<T= {'[NONE]'}>*<AT -= {'AS'}>*<AT -= {'LI'}>} Amount), AT))

[RP8]

Thanks for your comment! trying with aggr(), the expressions has no errors but unfortunately I'm not getting the desired results.

[JuanGerardo]

Hi @RP8 , I think what's happening is, if you include the dimension in the table, the If() condition is evaluated for every row and every row has a different AT value, so it works. But if you exclude the dimension from the table, AT is not a single value (unless you select only one), so you should use an aggregation function to get only one value for your comparison, for example:

IF(MaxString(AT) = 'RE'), -1, 1) * Sum({<SA = {'PL'}>*<LL = {'II7'}>*<T= {'[NONE]'}>*<AT -= {'AS'}>*<AT -= {'LI'}>} AMOUNT)

JG