QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for
Did you mean:
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%

 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

1 Solution

Accepted Solutions

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

talk is cheap, supply exceeds demand
2 Replies

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

talk is cheap, supply exceeds demand
Contributor
Author

Great work. Thanks for your help.

Marco

Community Browser