1 Reply Latest reply: Sep 11, 2013 10:29 PM by xiaogang zhang RSS

    Cumulative Values

      Dear all,

      I've a problem with cumulative values in two scenarios: Stock changes and Turnover, both by month.

      A wide varity of articles results in not having fact data for all articles and all months.

      Basically, using cumulative feature of QV Chart seems to be the solution, but it shows wrong overall month totals: the cumulated values of articles that have no value in a month are missing in that monts.

      As a simplified example it looks like this, using "full acumulation" and "sum(value)" or "value" as formula:

      hc_2012.01.30_14_27_23.png

      As there's no value for b (red) in November, b is not mentioned at this dimension element. The cumulated total for 11.11.2011 is simply wrong; it should be 400!

       

      One can apply a dirty fix by adding values with less significant decimals for any article and any month, but as there are multiple dimensions in the real app and the non-moving articles are the majority by far, this would let the data volume explode.

       

      Does anyone know a best practise for that? QV should be able to show right figures when it offers a cumulation feature out-of-the-box.

      The QVW for this excample is attached and a second row of charts shows the dirty-fixed example.

        • Re: Cumulative Values

          Hi All,

           

           

          I am new to Qlikview, can any one help me to do Cumulative sum and base on
          the sum result give the band range in Qlikview ,

           

           

          The input data is bellow:(only two columnssku_id and sales amount

           

           

          LOAD * INLINE [

           

           

          SKU_ID, sales

           

           

          sku_1,1

           

           

          sku_2,2

           

           

          sku_3,3

           

           

          sku_4,5

           

           

          sku_5,10

           

           

          sku_6,8

           

           

          sku_7,5

           

           

          sku_8,3

           

           

          sku_9,9

           

           

          sku_10,2

           

           

          sku_11,7

           

           

          sku_12,3

           

           

          sku_13,7

           

           

          sku_14,5

           

           

          sku_15,5

           

           

          sku_16,6

           

           

          ];

           

           

           

           

           

          I hope calculate above data base on sku_ID, Cumulative the sku sales order by sales amount desc ,output the band resulte
          as bellow:

          鏃犳爣棰�.jpg

          If the comulative_sum <30% and comulative_sum >0% then band is ‘A’
          ,else if comulative_sum >=30% and comulative_sum <60% then band is ‘B’  else ‘C’

           

           

          How to solve this problem

           

           

          Thanks in advance.