aggr function issue in set analysis and pivot table

[kkkumar82]

Hi all,

I have created two expressions

expr1 :

  min(aggr(Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}[CPN Savings])

  /

  Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend),[CM/ODM]))

expr2:

max(aggr(Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}[CPN Savings])

  /

  Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend),[CM/ODM]))

and also created a variable vCM/ODM = expr1 - expr2 , which is working fine when I used in a text box giving me the result 0.25%

which is (0.72 - .47) which we can see in the combo chart , ie the max and min %'s.

But when I am using the same expressions in  the pivot the minimum and maximum percentages are changed

I want 0.72% for all rows in max and 0.47% for all rows in min column, how to get this.

[rubenmarin · Accepted solution]

Hi Kiran, to avoid dimension filter you can use the TOTAL qualifier:

Max:

max(TOTAL aggr(Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}[CPN Savings])

    /

    Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend),[CM/ODM]))

Min:

min(TOTAL aggr(Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}[CPN Savings])

    /

    Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend),[CM/ODM]))

[kkkumar82]

Thanks alot Ruben.