Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
mwalter_yopeso
Contributor III
Contributor III

Rangesum() in Loading Script

Hello everybody,

I want to transfer a formula from a Dashboard to the loadingscript. The Data itself is stuctured as followed:

DayAmountterminated (boolean)
1100
120

1

1300
2201
2400
2600
31000

Right now I use the following formula in a Diagram:

RangeSum(above(sum({$<[terminated (boolean)]=1>}Amount]),0,rowno(total)))

and get the total aggregated amount for each Day, Day1 60, Day2 180, Day3 280 and thats perfect.

to transfer it to the script without loosing information, my first step was to add the column "terminated Amount" with the formula:

if(terminated (boolean)=1,Amount) as [terminated Amount]

using this script now:

load

Day,

sum(Amount),

sum([terminated (boolean)]) as [terminated (boolean)],

sum([terminated Amount]) as [terminated Amount]

resident XX

order by Day

group by Day;

brings me to the following table:

DayAmountterminated (boolean)terminated Amount
160120
2120120
310000

Now I want to add the aggregated amounts like I made it with Rangesum(), somehow it doesnt work in the Script.

The Table I want in the end looks like this:

DayAmountaggrAmountterminated AmountaggrTerminatedAmount
160602020
21201802040
3100280040

Can someone please help me with it? I'm sure its just a tiny error in my script, but it's consuming a lot of time now...

Thanks!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe

load

Day,

sum(Amount),

sum([terminated (boolean)]) as [terminated (boolean)],

sum([terminated Amount]) as [terminated Amount],

Rangesum( sum([terminated Amount]), Peek(aggrTerminatedAmount)) as aggrTerminatedAmount

resident XX

order by Day

group by Day;

View solution in original post

2 Replies
swuehl
MVP
MVP

Maybe

load

Day,

sum(Amount),

sum([terminated (boolean)]) as [terminated (boolean)],

sum([terminated Amount]) as [terminated Amount],

Rangesum( sum([terminated Amount]), Peek(aggrTerminatedAmount)) as aggrTerminatedAmount

resident XX

order by Day

group by Day;

View solution in original post

mwalter_yopeso
Contributor III
Contributor III
Author

Thank you Stefan!