Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

picturebox
Contributor

Problem with TOTAL in Pivot

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

1 Solution

Accepted Solutions
MVP
MVP

Problem with TOTAL in Pivot

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

2 Replies
MVP
MVP

Problem with TOTAL in Pivot

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

picturebox
Contributor

Problem with TOTAL in Pivot

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

Community Browser