Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using AGGR function to sum rows in a pivot table

Hi,

I haven't posted here before so hope that I can provide the correct information to help solve my issue.

I have created a variance analysis in Qlikview.  It allows the period actual result to be compared to the latest forecast and last year.  I am trying to split the variances into a simple rate and volume number.Each line of data that pulls into the table is unique, based on the dimension of StatementAccountNumber and PartNumber.

The issue that I am having is that I want to show the total of the individual expressions in a summary table.  The individual expressions calculate correctly, and I can get a correct total by using the "Sum of Rows" function in a simple table.  I now want to get the total for this to pull into a summary table on another sheet.  I do not believe that you can link totals from one QV sheet to another (like in excel).  After reading several blogs on this I believe that I have to use an AGGR function outlined below as, if I do not, then the expression calculates on the totals and does not equal the detail sheet (originally I had the individual data in a pivot table and had the same issue).  The below function is not calculating the correct figure either and I am not sure how to correct this.  I assumed that as the combination of StatementAccountNumber and PartNumber were unique, it would calculate each one individually and then add them together (as on the simple table).  I may be completely mis-understanding this function so any help would be greatly appreciated.

sum (aggr(distinct

((
if(sum(ValueInKG) = 0,
sum({$< Scenario={Actual}>}DirectLabourCostOfSale),
(
sum(DirectLabourCostOfSale)/(sum(ValueInKG)/1000))
*
((
Sum({$<Scenario = {Actual}>}ValueInKG)/1000)-(sum(ValueInKG)/1000)))))


,
StatementAccountNumber,PartNumber))

2 Replies
Gysbert_Wassenaar

Please post a small qlikview document that demonstrates the problem. See this document for more information: Preparing examples for Upload - Reduction and Data Scrambling


talk is cheap, supply exceeds demand
Not applicable
Author

I have outlined what is needed in the excel attachment below (picture of data below) as I am not confident enough around security of data to attach the QV document. The expression that I am working on is for the "Volume Variance".  You can see from the example below the difference in the data through it summing the individual lines versus the expression working on the totals.  I want to pull the total of the individual lines into the summary report so this reconciles to the individual report.

Capture.PNG