Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]))
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]))
Thanks alot Ruben.