topic Re: rolling up totals in pivot table in QlikView

rolling up totals in pivot table

nickm — Mon, 23 Feb 2015 18:03:09 GMT

Issue: Individual lines do not roll up with the correct total - see attached file column highlighted in gray.

Chart Type: Pivot Table

Background: We are taking the 2014 GP% calculation(2014_Margin_Dls/2014_Sales) as base starting point. There are specific product codes with 2 up charges (2 fields) which are added to the 2014 GP%. We then divide the 2014_GP% into the 2015 Budget to create the 2015_GP%_Goal. If there are no Sales for 2014 for a specific product code I multiply the 2015 Budget by .20 (20%). This is the issue, the individual lines are correct but the total rolls up incorrectly.

The calculation is in the column highlighted in gray (GP$ w Resin adj), any suggestions are greatly appreciated!

Thx - Nick

Re: rolling up totals in pivot table

rwunderlich — Sun, 01 Mar 2015 15:37:39 GMT

I think you are seeing the "Sum of Rows in Pivot Table" problem. Search the help for "Advanced aggregation" and you'll find a link to a Sum of Rows example.

The quick fix in your case is to enclose the entire expression in another sum(aggr()) like:

sum(

aggr(

...your current expresion...

,Territory,Customer_ID,Product_Code)

)

Example attached.

-Rob

http://masterssummit.com

Re: rolling up totals in pivot table

nickm — Sun, 01 Mar 2015 18:41:53 GMT

Rob,

Thank you very much for the correction..greatly appreciated.

Nick