topic Re: aggr function issue in set analysis and pivot table in QlikView

aggr function issue in set analysis and pivot table

kkkumar82 — Tue, 10 Feb 2015 07:48:23 GMT

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 — Tue, 10 Feb 2015 08:09:22 GMT

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 — Tue, 10 Feb 2015 09:09:41 GMT

Thanks alot Ruben.