topic Re: Cumulative sum in QlikView

Cumulative sum

Fri, 20 Nov 2015 09:08:58 GMT

Hello,

I'm doing a cumulative sum of the F2 field. But I need that sum to be done independently for each value of the F1 field.

Here is the example :

For F1 = 2, I would like the sum to start again from 0, not from 45.

Here is my script.

Input:

LOAD * INLINE [

F1, F2, F3

1, a, 5

1, b, -10

1, c, 50

2, a, 30

2, b, -6

2, c, 10

];

Output_temp:

LOAD F1

,F2

,F3

,RangeSum(F3,Peek(F3_Cumul)) as F3_Cumul

RESIDENT Input

ORDER BY F1,F2;

DROP TABLEs Input;

Thank you for your help.

Re: Cumulative sum

sunny_talwar — Fri, 20 Nov 2015 09:17:52 GMT

Try this:

Input:

LOAD * INLINE [

F1, F2, F3

1, a, 5

1, b, -10

1, c, 50

2, a, 30

2, b, -6

2, c, 10

];

Output_temp:

LOAD F1

,F2

,F3

,If(Previous(F1) = F1, RangeSum(F3,Peek(F3_Cumul)), F3) as F3_Cumul

RESIDENT Input

ORDER BY F1,F2;

DROP TABLEs Input;

Re: Cumulative sum

Fri, 20 Nov 2015 09:32:14 GMT

Thank you so much again Sunny !

You're the boss !