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

[piotrama]

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:

date	CumulativeSum
2018-01-01	10
2018-01-02	15
2018-01-03	17
2018-01-04	24

[felipedl · Accepted solution]

HI Piotr,

As for your data the following will apply:

date	value	RangeSum(value;peek(value)) as CumulativeSum	peek(CumulativeSum)
2018-01-01	10	10	-
2018-01-02	5	10+5	10
2018-01-03	2	15+2	15
2018-01-04	7	17+7	17

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]

Hi Felip,

thanks for clarifying, now I finally get the concept

[felipedl]

Glad it helped .

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

Felipe.