Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

marcorieben
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:

CountryMaterialYearPrice
DEMaterial_A2011100
DEMaterial_B2011200
DEMaterial_C2011250
DEMaterial_B2012200
DEMaterial_C2012200

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

Material20112012Realization
Total550400-27.27%
Material_A1000-100.00%
Material_B2002000.00%
Material_C250200-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%.

Material20112012RealizationCorrect realization
450400-27.27%11.11%
Material_B2002000.00%
Material_C250200-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

1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

Re: Subtotal calculation for price evolution

hi have a look at the attach file

i used aggr function

2 Replies
lironbaram
Honored Contributor II

Re: Subtotal calculation for price evolution

hi have a look at the attach file

i used aggr function

marcorieben
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?

Material20112012Realization2011 Volume2012 VolumeUnitary Price 2011Unitary Price 2012Volume 2012 * Unitary Price 2011Difference (expression total) - wrongDifference (sum of rows) - correct Price Quality (wrong)Price Quality (correct)
Total1'6505'200215.15%10291651794'7854151'3008.7%27.2%
Material_D4004000.00%24200100800-400-400-50.0%-50.0%
Material_E5005000.00%21250500250250250100.0%100.0%
Material_C5501'900245.45%4121381581'65025025015.2%15.2%
Material_B2002'4001'100.00%2121002001'2001'2001'200100.0%100.0%

Again, many thanks for the help!

Marco

Community Browser