Create cumulative numbers in load script

[mikecherry]

Hi all,

I am trying to get cumulative numbers in the load script like I have calculated using rangesum in the table below.

You can find attached the app and the data.

[DesmoArka]

hi

have you try with peek function?

something like this:

tab:
LOAD * INLINE [
date, value
1, 3
2, 4
3, 3
4, 5
5, 7
6, 1
];

cumulate:
LOAD
date,
value,
RangeSum(value,Peek(cumul,-1)) as cumul

Resident tab
Order by date asc;

DROP Table tab;

I hope this help you

[mikecherry]

Yes but it doesn't give me the right number as I have also two other dimensions I need to consider (Entity and Currency) 

[DesmoArka]

if you have more aggr dimension you have to check if the actual row is equal to previous one:

tab:
LOAD * INLINE [
entity, date, value
a, 1, 3
a, 2, 4
a, 3, 3
b, 4, 5
b, 5, 7
b, 6, 1
];

cumulate:
LOAD
entity,
date,
value,
if(Peek(entity,-1)<> entity,value, RangeSum(value,Peek(cumul,-1))) as cumul

Resident tab
Order by entity,date asc;

DROP Table tab;

result:

[DesmoArka]

maybe this is what you want:

tab:
LOAD * INLINE [
entity, date, value,currency
a, 1, 3, e
a, 2, 4, d
a, 3, 3, d
a, 3, 3, e
a, 3, 3, e
b, 4, 5, d
b, 5, 7, d
b, 6, 1, e
];

cumulate:
LOAD
entity,
date,
value,
currency,
if(Peek(entity,-1)<> entity or Peek(currency,-1)<> currency, value , RangeSum( value, Peek(cumul,-1))) as cumul
Resident tab
Order by entity,currency,date asc;

DROP Table tab;

result:

See you.

[IamAlbinAntony]

use this in back end script , peek function can use data from memory unlike previous function.

alt(Peek (cumul)+value ,value ) as cumul

[mikecherry]

I don't know if you guys can spot something wrong but it doesn't want to work... 

Snip of the pivot showing first lines.. it works at the beginning but as soon another currency comes in the numbers go crazy. Column in the middle is Rangesum in the object and the column on the right is Sum(Trans_Cumulative)..

[DesmoArka]

HI,

in your image i don't see order by in load, may you post that?

[mikecherry]

Here you go 😄 

[mikecherry]

If you check the app I have just created, which is really similar to my scenario it doesn't work either.