Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
marcorieben
Contributor
Contributor

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

View solution in original post

marcorieben
Contributor
Contributor
Author

Great work. Thanks for your help.

Marco