Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
mikecherry
Contributor III
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.

mikecherry_0-1588851022841.png

 

1 Solution

Accepted Solutions
DesmoArka
Partner
Partner

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

View solution in original post

13 Replies
DesmoArka
Partner
Partner

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
Contributor III
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) 

DesmoArka
Partner
Partner

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_0-1588854301329.png

 

DesmoArka
Partner
Partner

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:

DesmoArka_0-1588855453954.png

 

See you.

FruzoCrew
Creator
Creator

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

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

mikecherry
Contributor III
Contributor III
Author

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

 

mikecherry_0-1588930738986.png

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

 

mikecherry_1-1588930900866.png

 

 

 

DesmoArka
Partner
Partner

HI,

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

mikecherry
Contributor III
Contributor III
Author

Here you go 😄 

 

mikecherry_0-1588937743747.png

 

mikecherry
Contributor III
Contributor III
Author

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