Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jmurrevcyc
Contributor III
Contributor III

RangeSum(Above) equivalent in Qlik Sense Load Script

I am loading in a file that just has monthly values (fields highlighted in yellow in the below screenshot:

I am trying to return the value being shown in the green highlighted field, which is aggregating based on the month columns (asc).

I can get this to work on the Front End using the RangeSum(Above function

used the set operations in the load script editor, and then in the dashboard, I made the expression as $(accum) [value] $(/accum)

In the example below, there are two cust_num's with a month range of 2022-06 - 2022-12.

The accumulated value for 2022-12 should be 1,760 for cust_num 1, and 2,090 cust_num 2

And this is correct as long as all months are showing, and the table is sorted by cust_num, month

But if try to select just one month, it only shows the value for that month. So if you filter for 2022-12, you would not see 1,760, and 2,090, but rather you would see 200, and 400 (Just the value for 2022-12).

https://community.qlik.com/t5/App-Development/Accumulative-Values/td-p/31464

image.png

Because of this issue, I am trying to do the calculation in the load script. Because Above is not a recognized function in the QlikSense load script editor, the alternative is to use a peek method. So I have tried this:

if(previous("cust_num") = "cust_num", rangesum(peek(value_aggr), (value)),value) as value_aggr

if(previous("month") = "month", rangesum(peek(value_aggr), (value)),value) as value_aggr

if(previous("month","cust_num") = "month", rangesum(peek(value_aggr), (value)),value) as value_aggr

if(previous("month") = "month", if (previous("cust_num") = "cust_num",
rangesum (peek("value_aggr"), "value"), "value"))
as value_aggr

None of the load script have worked as of yet.

Labels (2)
1 Solution

Accepted Solutions
KGalloway
Creator II
Creator II

Have you tried using an order by statement in the table where you do the accumulating? Something like the following:

original_table:

load

cust_num,

month,

value

from original_source;

 

noconcatenate

table_with_sum:

load

*,

if(previous(cust_num) = cust_num, rangesum(peek('value_aggr'), value), value) as value_aggr

resident original_table

order by cust_num, month

;

View solution in original post

2 Replies
KGalloway
Creator II
Creator II

Have you tried using an order by statement in the table where you do the accumulating? Something like the following:

original_table:

load

cust_num,

month,

value

from original_source;

 

noconcatenate

table_with_sum:

load

*,

if(previous(cust_num) = cust_num, rangesum(peek('value_aggr'), value), value) as value_aggr

resident original_table

order by cust_num, month

;

jmurrevcyc
Contributor III
Contributor III
Author

Thank you for the advice. I think I had a parentheses in the wrong place, which you helped to point out.

I also decided to encase the value_aggr in single quotes.

However neither of those two changes affected the outcome.

But I think what made it work was I needed to have the current field before the previous(field)

vs.

if(previous(cust_num) = "cust_num"

So like below:

if("cust_num" = previous("cust_num"),RangeSum(peek('value_aggr'), value),value) as value_aggr

 

Thank you for your help on getting me on the right track.

This is where I got the idea to reverse the field comparison

https://community.qlik.com/t5/QlikView-App-Dev/accumulation-in-load-script/td-p/1455017