Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Search instead for
Did you mean:
Not applicable

## Accumulative Sum in Script

hi guys,

I have

my data goes from 2007 untill today.

actually I have in my Expressions the accumulative sum- > Range(above(sum(Amount_actual)),0,rowno()) ..

But that depends on the first Amount ..

how can I get the accumulated amounts on each date  in the script -> get the stock value accumulated on each day ..

thanks forward

3 Replies
Master II

If your data is ordered by date ascending, you can do something like:

Rangesum(StockValue,peek('StockValue',-1) )as StockAccum;

Not applicable
Author

does not work

it's order by date asc

Contributor III

Hi, I had similar task and tried to find solution here. I didn't find it, but one of discussion helped me to solve this problem. I understand that it was almost 1 year ago, but it can be helpful for further work .

BaseInfo:

LOAD

RowNo() as NumberOfRow,

Parameter1,

Amount

Resident

PreLoadedTable

Order By

Amount desc;

LET varAmountOfParameter1 = FieldValueCount('Parameter1');

For i = 1 to varAmountOfParameter1

varParameter1      = FieldValue('Parameter1',i);

varNumberOfRow  = FieldValue('NumberOfRow',i);

AccumulativeSums:

LOAD

'\$(varParameter1)'  as varParameter1,

Sum(Amount)       as AmountAccumulatively

Resident

BaseInfo

Where

NumberOfRow <= '\$(varNumberOfRow)'

Group By

'\$(varParameter1)';

NEXT

NoConcatenate

ResultTable:

LOAD

varParameter1,

Amount

Resident

BaseInfo;

Join

LOAD

varParameter1,

AmountAccumulatively

Resident

AccumulativeSums;

DROP Tables AccumulativeSums, BaseInfo;

As a result we obtain additional column with accumulative amount. I hope it will help .

Community Browser