topic Problem with TOTAL in Pivot in QlikView

Problem with TOTAL in Pivot

picturebox — Wed, 01 Feb 2012 15:08:15 GMT

Hi all,

In a pivot chart I have a number of dimensions and then the calculated column Sum Outlets. At the lowest level the calculation is correct, this is what I am using:

= if (sum(leh_EU_YTD_CUR)=0, sum(Umsatz2011), sum(leh_EU_YTD_CUR))

It makes sense in QV that the total for a dimension uses the formula as above, but what I really want is the total of the rows for the dimension and column Sum Outlets based on the values calculated. If I do a quick change to flat table, the totals are set on "rows", the total is correct. How can I get that to function in a pivot table?

Thanks,Petra

Problem with TOTAL in Pivot

swuehl — Wed, 01 Feb 2012 15:19:27 GMT

If I understand your problem correctly, you need advanced aggregation to get the sum of rows in your pivot table (there is example in the Help, search for sum of rows in pivot table), like

=sum( aggr( if (sum(leh_EU_YTD_CUR)=0, sum(Umsatz2011), sum(leh_EU_YTD_CUR)) , YOURTABLEDIMENSION1, YOURTABLEDIMENSION2))

Just add all your dimensions of the table to the aggr dimension list.

Expression should also work in a straight table.

Hope this helps,

Stefan

Problem with TOTAL in Pivot

picturebox — Wed, 01 Feb 2012 15:41:43 GMT

Hi,
works like a charm. I had tried to make sense of the aggr() function before, now I see the use and results.
Thanks a lot!
Petra