topic Re: Create Cumulative numbers in load script in App Development

Create cumulative numbers in load script

mikecherry — Sat, 16 Nov 2024 18:41:02 GMT

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.

Re: Create bleep numbers in load script

DesmoArka — Thu, 07 May 2020 12:02:54 GMT

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

Re: Create bleep numbers in load script

mikecherry — Thu, 07 May 2020 12:04:40 GMT

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

Re: Create bleep numbers in load script

DesmoArka — Thu, 07 May 2020 12:25:19 GMT

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:

Re: Create bleep numbers in load script

DesmoArka — Thu, 07 May 2020 12:44:26 GMT

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.

Re: Create cumulative numbers in load script

IamAlbinAntony — Thu, 07 May 2020 13:42:30 GMT

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

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

Re: Create Cumulative numbers in load script

mikecherry — Fri, 08 May 2020 09:42:59 GMT

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

Re: Create Cumulative numbers in load script

DesmoArka — Fri, 08 May 2020 09:53:28 GMT

HI,

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

Re: Create Cumulative numbers in load script

mikecherry — Fri, 08 May 2020 11:36:43 GMT

Here you go 😄

Re: Create Cumulative numbers in load script

mikecherry — Fri, 08 May 2020 11:49:06 GMT

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

Re: Create Cumulative numbers in load script

DesmoArka — Fri, 08 May 2020 12:30:34 GMT

I use your script at all.

I thing you are wrong only graph and not script.

Create this pivot and check if it's ok:

You must have Bunit in Graph

Re: Create Cumulative numbers in load script

DesmoArka — Fri, 08 May 2020 12:33:31 GMT

This is my file.

Re: Create Cumulative numbers in load script

DesmoArka — Fri, 08 May 2020 13:01:40 GMT

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

Re: Create Cumulative numbers in load script

mikecherry — Tue, 12 May 2020 08:09:16 GMT

Thank you man ! In the end I had to do a group by before your script as I had multiple transactions at the same date but after that I have used your script and works great !

Ciao

Michele

topic Re: Create Cumulative numbers in load script in App Development

Create cumulative numbers in load script

Re: Create **bleep** numbers in load script

Re: Create **bleep** numbers in load script

Re: Create **bleep** numbers in load script

Re: Create **bleep** numbers in load script

Re: Create cumulative numbers in load script

Re: Create Cumulative numbers in load script

Re: Create Cumulative numbers in load script

Re: Create Cumulative numbers in load script

Re: Create Cumulative numbers in load script

Re: Create Cumulative numbers in load script

Re: Create Cumulative numbers in load script

Re: Create Cumulative numbers in load script

Re: Create Cumulative numbers in load script

Re: Create bleep numbers in load script

Re: Create bleep numbers in load script

Re: Create bleep numbers in load script

Re: Create bleep numbers in load script