topic Re: How to achieve cumulative sum from current record in qlik script. in App Development

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

muktasrivastava — Sat, 27 Jul 2019 10:09:34 GMT

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

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

swarnendu — Sat, 27 Jul 2019 11:49:17 GMT

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

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

muktasrivastava — Tue, 30 Jul 2019 07:13:13 GMT

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?

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

Frank_Hartmann — Tue, 30 Jul 2019 07:46:41 GMT

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;

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

muktasrivastava — Tue, 30 Jul 2019 07:51:02 GMT

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

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

Frank_Hartmann — Tue, 30 Jul 2019 08:12:09 GMT

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;

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

muktasrivastava — Tue, 30 Jul 2019 08:21:02 GMT

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