Using AGGR function to sum rows in a pivot table

Wed, 30 Sep 2015 18:46:47 GMT

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))

Re: Using AGGR function to sum rows in a pivot table

Gysbert_Wassenaar — Thu, 01 Oct 2015 05:41:54 GMT

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

Re: Using AGGR function to sum rows in a pivot table

Thu, 01 Oct 2015 07:49:26 GMT

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.

topic Re: Using AGGR function to sum rows in a pivot table in QlikView

Using AGGR function to sum rows in a pivot table

Re: Using AGGR function to sum rows in a pivot table

Re: Using AGGR function to sum rows in a pivot table