2 Replies Latest reply: Nov 20, 2016 8:13 AM by Achates Chen RSS

    How to do with progressive calculation?

    Achates Chen

      Hi there!

      Can someone help me on this challenge?

      Please see below example. First three columns are raw data. I want to progressively sum the amount of each item base on the date in ascending order to get the result as last column.

      I am quite new in Qlikview, have no idea how to achieve this.

      Thank you!

         

      Itemdateamountprogressive sum per item
      A1-Oct1010
      A2-Oct1525
      B3-Oct33
      A4-Oct530
      C5-Oct66
      D6-Oct77
      C7-Oct410
      B8-Oct2326
      A9-Oct838
        • Re: How to do with progressive calculation?
          Gysbert Wassenaar
          // load the source data in a temporary table
          Temp:
          LOAD
               Item,
               date,
               amount
          FROM
               sourcefile
               ;
          
          // sort the data on Item first and date second using an order by
          Result:
          LOAD
               Item,
               date,
               amount,
          
          /*  calculate the cumulative sum by checking if the item of the current record
               is the same as that of the previous record. If it's the same then get the current 
               cumulative value from the target table using peek and add the current amount
               to it. If it isn't the store amount of the current record in the cumulative field.      */
          
               if(Previous(Item)=Item,
                    rangesum(amount, peek([progressive sum per item])),
                    amount) as [progressive sum per item]
          
          RESIDENT
               Temp
          ORDER BY
               Item, date
               ;
          
          // drop the temporary table
          DROP TABLE Temp;