Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor III

## Create cumulative numbers in load script

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.

Labels (7)

• ### sunny talwar

1 Solution

Accepted Solutions
Partner - Contributor III

and remenber that you are summing a field with cumulated value, u can't use that to total calculation.

Add this to the script:

left Join
load max(TransValueDate) as TransValueDate,
BUnit,
TransCcy,
1 as max_data
Resident Transactions1
Group by BUnit,
TransCcy;

and use this measure in my pivot:

if (Dimensionality()<=1, sum(aggr(Sum({<max_data={1}>}Trans_CUM),BUnit,TransCcy)), Sum(Trans_CUM))

I attached all file

13 Replies
Partner - Contributor III

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:
date,
value,
RangeSum(value,Peek(cumul,-1)) as cumul

Resident tab
Order by date asc;

DROP Table tab;

Contributor III
Author

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

Partner - Contributor III

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:
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:

Partner - Contributor III

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:
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.

Creator

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

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

Contributor III
Author

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)..

Partner - Contributor III

HI,

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

Contributor III
Author

Here you go 😄

Contributor III
Author

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

Tags
Community Browser