Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
// 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;
// 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;
Hi Gysbert!
Thank you so much for your quick answer! It solved my problem perfectly!
This is really an amazing community!