Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

piotrama
New 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
Tags (1)
1 Solution

Accepted Solutions
felipedl
Valued Contributor III

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

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

3 Replies
felipedl
Valued Contributor III

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

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
New Contributor II

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

Hi Felip,

thanks for clarifying, now I finally get the concept

felipedl
Valued Contributor III

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

Glad it helped .

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

Felipe.