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.
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
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
Yes but it doesn't give me the right number as I have also two other dimensions I need to consider (Entity and Currency)
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:
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.
use this in back end script , peek function can use data from memory unlike previous function.
alt(Peek (cumul)+value ,value ) as cumul
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)..
HI,
in your image i don't see order by in load, may you post that?
Here you go 😄
If you check the app I have just created, which is really similar to my scenario it doesn't work either.