# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
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 (8)

• ### sunny_talwar

1 Solution

Accepted Solutions
Partner

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

left Join
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

hi

have you try with peek function?

something like this:

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

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

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

maybe this is what you want:

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

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