Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
RP8
Contributor
Contributor

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

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

3 Replies
GaryGiles
Specialist
Specialist

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
Contributor
Contributor
Author

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

JuanGerardo
Partner - Specialist
Partner - Specialist

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