Cumulative sum with negative values

[#1]

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.

[sunny_talwar]

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

[#3]

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;

[#4]

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;

[sunny_talwar · Accepted solution]

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;

[#6]

Great ! Thank you so much

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

But your solution is far better