Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

sum of rows aggregation for subtotal

Dear QlikView community,

My issue is that I am trying to take into account the volumes of the different years for the price quality expression. I have enclosed my files, which might make it easier to comprehend the crux.

These are the steps that I am undertaking:

1. Calculate unitary prices (sales / volume) for both years, only taking into account prices of materials, which exist in both years. ==>works fine

2. Volume 2012 * Unitary Price 2011 (this column will then be used to calculate the overall price quality) . ==> works fine

3. Difference (2012 sales - Volume 2012 * Unitary Price 2011) ==>please see the different subtotals

4. Price Quality (Difference / Volume 2012 * Unitary Price 2011) ==>given that QV takes the wrong difference subtotal, my result at subtotal level is incorrect.

I should really get an aggregation on the difference column, which I could reuse for the Price Quality calculation.

My overall price quality for this scenario, as defined by step 4, should not be 8.70% (this is just from Excel) but rather:

1'300 / 4'785 = 27.20%

Material20112012Realization2011 Volume2012 VolumeUnitary Price 2011Unitary Price 2012Volume 2012 * Unitary Price 2011Difference (expression total) - wrongDifference (sum of rows) - correct Price Quality (wrong)Price Quality (correct)
1'6505'200215.15%10291651794'7854151'3008.70%27.20%
Material_D4004000.00%24200100800-400-400-50.00%-50.00%
Material_E5005000.00%21250500250250250100.00%100.00%
Material_C5501'900245.45%4121381581'65025025015.20%15.20%
Material_B2002'4001100.00%2121002001'2001'2001'200100.00%100.00%

Any help is greatly appreciated.

Marco

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Basically you need to add a sum(aggr( ... , Material)) around your Difference expression. See attached qvw.


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

Basically you need to add a sum(aggr( ... , Material)) around your Difference expression. See attached qvw.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Great work. Thanks for your help.

Marco