Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
nickm
Contributor
Contributor

rolling up totals in pivot table

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

1 Solution

Accepted Solutions
rwunderlich

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

View solution in original post

2 Replies
rwunderlich

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

nickm
Contributor
Contributor
Author

Rob,

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

Nick