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.
==> The correct result at subtotal level should be (400 / 450) - 1 = 11.11%.
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.
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)|
Again, many thanks for the help!