topic How to do with progressive calculation? in QlikView

How to do with progressive calculation?

achates2008 — Sun, 20 Nov 2016 12:58:18 GMT

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

Re: How to do with progressive calculation?

Gysbert_Wassenaar — Sun, 20 Nov 2016 13:02:20 GMT

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

Re: How to do with progressive calculation?

achates2008 — Sun, 20 Nov 2016 13:13:35 GMT

Hi Gysbert!

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

This is really an amazing community!