Subtotal calculation for price evolution

[Anonymous]

Dear QlikView community,

I am having difficulties with calculating a price evolution, whilst trying to filter out transactions, which contained values in both compared years.

Please find enclosed the zip file with the raw data and the qvw with more details and soltution attempts.

• Raw data:

Country	Material	Year	Price
DE	Material_A	2011	100
DE	Material_B	2011	200
DE	Material_C	2011	250
DE	Material_B	2012	200
DE	Material_C	2012	200

• Scenario 1 - Regular view (without filter on zero values)

Material	2011	2012	Realization
Total	550	400	-27.27%
Material_A	100	0	-100.00%
Material_B	200	200	0.00%
Material_C	250	200	-20.00%

• Scenario 2 - View on table with filtering zero values if comparing year contains zero values. The realization at subtotal level however, uses the same calculation as in scenario 1, giving me a wrong picture of my overall realization because I am only interested in the subtotal of materials, where values exist in both years.

          ==> The correct result at subtotal level should be (400 / 450) - 1 = 11.11%.

Material	2011	2012	Realization	Correct realization
	450	400	-27.27%	11.11%
Material_B	200	200	0.00%
Material_C	250	200	-20.00%

I cannot filter these values out in the script because the solution should be flexible and adapt to whichever year is selected.

Your help is greatly appreciated.

Kind regards,

Marco

[lironbaram · Accepted solution]

hi have a look at the attach file

i used aggr function

[Anonymous]

Thank you very much for your help.

The realization with only taking in consideration the material, which were sold in both years works great with the aggr function.

Trying to get to my final result, I need to compare the annual unitary prices with the different volumes.

The difference of the column actual 2012 Price - (Volume 2012 * unitary 2011) should then be used to identify the overall price quality of my products.

This works fine at material level; however at subtotal on the price quality QV takes the difference total, based on expression total and not sum of rows total.

I have again enclosed my example. In the excel you can see how it should correctly get calculated, I assume that I have to use a different type of aggregation expression in order to capture the correct subtotal difference for the price quality calculation.

Is there a way to aggregate on the column Difference?

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)
Total	1'650	5'200	215.15%	10	29	165	179	4'785	415	1'300	8.7%	27.2%
Material_D	400	400	0.00%	2	4	200	100	800	-400	-400	-50.0%	-50.0%
Material_E	500	500	0.00%	2	1	250	500	250	250	250	100.0%	100.0%
Material_C	550	1'900	245.45%	4	12	138	158	1'650	250	250	15.2%	15.2%
Material_B	200	2'400	1'100.00%	2	12	100	200	1'200	1'200	1'200	100.0%	100.0%

Again, many thanks for the help!

Marco

[geam1988]

Hello,

I am also trying to do same kind of report. Is your problem solved? 

Thanks

Kazi