Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
piotrama
Contributor II
Contributor II

How does Cumulative Sum work with RangeSum and Peek in script editor?

Hi,

this a bit different question: I found a script showing how to calculate a cumulative sum in load editor using RangeSum() and Peek(). Although I know both these functions I can't understand how they work together and return a proper Cumulative Sum. Can someone explain me how Peek() works as an argument in RangeSum()?

The concept is:

test:

Load

date,

RangeSum(value,Peek(CumulativeSum)) as CumulativeSum

;

Load * Inline [

date,value

2018-01-01,10

2018-01-02,5

2018-01-03,2

2018-01-04,7

];

The output is:

dateCumulativeSum
2018-01-0110
2018-01-0215
2018-01-0317
2018-01-0424
1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

HI Piotr,

As for your data the following will apply:

datevalueRangeSum(value;peek(value)) as CumulativeSumpeek(CumulativeSum)
2018-01-011010-
2018-01-02510+510
2018-01-03215+215
2018-01-04717+717

It's a visual interpretation of what it will do.

Just don't forget to order your data by date field, like:

test:

Load * Inline [

date,value

2018-01-01,10

2018-01-02,5

2018-01-03,2

2018-01-04,7

];

finalData:

Load

*,

RangeSum(value,Peek(CumulativeSum)) as CumulativeSum

Resident test

order by date;

drop table test;

or esle the peek function will get quite weird behavior on the data.

Hope it helps

View solution in original post

3 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

HI Piotr,

As for your data the following will apply:

datevalueRangeSum(value;peek(value)) as CumulativeSumpeek(CumulativeSum)
2018-01-011010-
2018-01-02510+510
2018-01-03215+215
2018-01-04717+717

It's a visual interpretation of what it will do.

Just don't forget to order your data by date field, like:

test:

Load * Inline [

date,value

2018-01-01,10

2018-01-02,5

2018-01-03,2

2018-01-04,7

];

finalData:

Load

*,

RangeSum(value,Peek(CumulativeSum)) as CumulativeSum

Resident test

order by date;

drop table test;

or esle the peek function will get quite weird behavior on the data.

Hope it helps

piotrama
Contributor II
Contributor II
Author

Hi Felip,

thanks for clarifying, now I finally get the concept

felipedl
Partner - Specialist III
Partner - Specialist III

Glad it helped .

I forgot to add the order by date on the statement so I edited the post.

Felipe.