# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor

## Subtotal calculation for price evolution

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.

Kind regards,

Marco

1 Solution

Accepted Solutions
Honored Contributor II

## Re: Subtotal calculation for price evolution

hi have a look at the attach file

i used aggr function

2 Replies
Honored Contributor II

## Re: Subtotal calculation for price evolution

hi have a look at the attach file

i used aggr function

New Contributor

## Re: Subtotal calculation for price evolution

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