topic Re: Cumulative sum with negative values in QlikView

Cumulative sum with negative values

Thu, 19 Nov 2015 15:44:32 GMT

Hello,

I need to do a cumulative sum, but when there is a negative value, I want it to be subtracted.

Here is what i have done :

RangeSum(EarnBurn_Journée,Peek(EarnBurn_Journée_Cumul))

as EarnBurn_Journée_Cumul

You see that when it encounters "-600", the cumul is made on all dates and then it continues from "1162".

What I need is (see right table) :

...

1162

562

635

...

Does anyone know how i can handle it ?

Thank you.

Re: Cumulative sum with negative values

sunny_talwar — Thu, 19 Nov 2015 16:01:47 GMT

I think it seems to be another issue, the expression you are using seems to be right. Can you share the complete script?

Re: Cumulative sum with negative values

Thu, 19 Nov 2015 16:06:10 GMT

Hi Sunny,

Thanks for your help.

AGGR_SoldePoints_temp:

LOAD PassId_an

,AccountTransactionValueValidityStartDate_DATE as AGGR_Account_Date

,AccountTransactionValueValidityStartDate_AnnéeMois as AGGR_Account_AnnéeMois

,SUM(AccountTransactionValue) as EarnBurn_Journée

RESIDENT F_AccountTransaction

WHERE RuleCode<>'VISITEMAGASIN'

GROUP BY PassId_an,AccountTransactionValueValidityStartDate_AnnéeMois, AccountTransactionValueValidityStartDate_DATE;

AGGR_Solde:

LOAD PassId_an

,AGGR_Account_AnnéeMois

,AGGR_Account_Date

,RangeSum(EarnBurn_Journée,Peek(EarnBurn_Journée_Cumul)) as EarnBurn_Journée_Cumul

RESIDENT AGGR_SoldePoints_temp;

DROP TABLE AGGR_SoldePoints_temp;

Re: Cumulative sum with negative values

Thu, 19 Nov 2015 16:34:35 GMT

Same situation with a very simple example :

Input:

LOAD * INLINE [

F1, F2

a, 5

a, 10

b, 50

b, 6

b, -20

c, 30

d, 6

d, 8

e, -10

];

Output:

LOAD F1

,F2

,RangeSum(F2,Peek(F2_Cumul)) as F2_Cumul

RESIDENT Input;

DROP TABLE Input;

Re: Cumulative sum with negative values

sunny_talwar — Thu, 19 Nov 2015 16:41:37 GMT

I think it is an order issue. The numbers are getting Summed properly, you just need to add a order by statement in your resident load.

Look at this script:

Input:

LOAD *,

RowNo() as UK;

LOAD * INLINE [

F1, F2

a, 5

a, 10

b, 50

b, 6

b, -20

c, 30

d, 6

d, 8

e, -10

];

Output:

LOAD F1

,F2

, UK

,RangeSum(F2,Peek(F2_Cumul)) as F2_Cumul

RESIDENT Input

Order By UK;

DROP TABLE Input;

Re: Cumulative sum with negative values

Thu, 19 Nov 2015 16:58:28 GMT

Great ! Thank you so much

I just found a solution with FABS() function and an other intermediate table...

But your solution is far better