Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Basically you need to add a sum(aggr( ... , Material)) around your Difference expression. See attached qvw.
Basically you need to add a sum(aggr( ... , Material)) around your Difference expression. See attached qvw.
Great work. Thanks for your help.
Marco