How to achieve cumulative sum from current record in qlik script.

[muktasrivastava]

Hi All, I am looking for a way to achieve cumulative sum of all records from current position.

For example:

Product            Sales                    Cumulative Sum

A                             10                             65

B                              5                              55

C                             20                             50

D                            30                             30

Thanks

[swarnendu]

Try this                  Sum(TOTAL Sales) - RangeSum(Above(Sum(Sales), 1, RowNo()))

[muktasrivastava]

This is not working. Also, i want it at script level where we can't use above. Do you have any other way to achieve it?

[Frank_Hartmann]

try like this:

TABLE:
LOAD * INLINE [
Product,Sales 
A,10  
B,5 
C,20 
D,30
];

Table2:
LOAD Product,
Sales,
RangeSum(Peek('VALUE_TOTAL'), Sales) as VALUE_TOTAL
Resident TABLE Order By Product desc;DROP Table TABLE;

[muktasrivastava]

Thanks Frank_Hartmann i am able to achieve it with this method. Now i have another issue. Is there a way to apply peek on group of dimension. For example: TABLE: LOAD * INLINE [ Product,Sales A,10 A,5 A,10 B,5 B,20 B,100 C,20 C,10 D,30 D,50 ]; and get cumulative sum of A,B,C & D

[Frank_Hartmann · Accepted solution]

try this:

TABLE:
Load *, RowNo() as Row;
LOAD * INLINE [
Product,Sales 
A,10  
A,12
A,1
B,5 
B,25
C,20 
C,33
D,30
D,34
];

Table2:
LOAD Product,
Sales,
If(Product=Previous(Product), RangeSum(Peek('VALUE_TOTAL'), Sales), Sales) as VALUE_TOTAL
Resident TABLE Order By Product desc;DROP Table TABLE;

[muktasrivastava]

Thanks it was helpful and i was able to achieve the result with your solution.