sum of rows aggregation for subtotal

[Anonymous]

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%

Material	2011	2012	Realization	2011 Volume	2012 Volume	Unitary Price 2011	Unitary Price 2012	Volume 2012 * Unitary Price 2011	Difference (expression total) - wrong	Difference (sum of rows) - correct	Price Quality (wrong)	Price Quality (correct)
	1'650	5'200	215.15%	10	29	165	179	4'785	415	1'300	8.70%	27.20%
Material_D	400	400	0.00%	2	4	200	100	800	-400	-400	-50.00%	-50.00%
Material_E	500	500	0.00%	2	1	250	500	250	250	250	100.00%	100.00%
Material_C	550	1'900	245.45%	4	12	138	158	1'650	250	250	15.20%	15.20%
Material_B	200	2'400	1100.00%	2	12	100	200	1'200	1'200	1'200	100.00%	100.00%

Any help is greatly appreciated.

Marco

[Gysbert_Wassenaar · Accepted solution]

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

[Anonymous]

Great work. Thanks for your help.

Marco