2 Replies Latest reply: May 19, 2013 7:47 AM by Marco Rieben RSS

    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

        • Re: Subtotal calculation for price evolution
          Liron Baram

          hi have a look at the attach file

          i used aggr function

            • 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