Rangesum in script for last 2 days

ariejan — Wed, 29 Jun 2022 14:35:19 GMT

Hi,

I need to calculate the RangeSum for the last 2 days where Segment and Article is the same.

I did find a way to make a cumulative sum, but that only works for all dates.

Below is an example of my dataset. In reality the dataset has millions of rows and i have to calculate for the last 6 months, but for this example let's use 2 days.

Data:
Load * Inline [
Segment|Article|Date|Amount
X|1|15-05-2022|2
X|1|14-05-2022|4
X|1|13-05-2022|6
X|2|15-05-2022|12
X|2|14-05-2022|14
X|2|13-05-2022|16
Y|1|15-05-2022|3
Y|1|14-05-2022|5
Y|1|13-05-2022|7
Y|2|15-05-2022|13
Y|2|14-05-2022|15
Y|2|13-05-2022|17
] (delimiter is '|');

CumulativeSum:
Load *,
if(peek(Segment)=Segment and peek(Article)=Article,rangesum(peek(CumulativeSum),Amount),Amount) as CumulativeSum

Resident Data
Order by Article, Segment, Date;
Drop table Data;

topic Rangesum in script for last 2 days in App Development

Rangesum in script for last 2 days