How to do with progressive calculation?

[achates2008]

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!

Item	date	amount	progressive sum per item
A	1-Oct	10	10
A	2-Oct	15	25
B	3-Oct	3	3
A	4-Oct	5	30
C	5-Oct	6	6
D	6-Oct	7	7
C	7-Oct	4	10
B	8-Oct	23	26
A	9-Oct	8	38

[Gysbert_Wassenaar · Accepted solution]

// 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;

[achates2008]

Hi Gysbert!

Thank you so much for your quick answer! It solved my problem perfectly!

This is really an amazing community!