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: 
melissapluke
Partner - Creator
Partner - Creator

Rolling Sum

I would like to create a rolling sum to display in a bar graph. Currently, I have a Date and Amount.  I would like to sum the data for that day and the previous 2 days to create a "Running total" for a day.  For example, on May 4 the Running Total would be 11.  On May 3rd, the running total would be 13.  On May 2nd, the Running total would be 10, because there is no date on 4/30.  I wasn't sure how to create this and whether the best way to do this would be to create some sort of new field in the load script or via set analysis.  The attachment is simply the table displayed below in excel format. Thanks for the help!

 

TransactionMonthDateAmount
1Jan1/1/20175
2Jan1/1/201715
3Jan1/2/20174
4Jan1/3/20177
5Jan1/4/20173
6Feb2/1/20175
7Feb2/2/20178
8Feb2/3/20174
9Feb2/4/20179
10Mar3/1/20176
11Mar3/2/20173
12Mar3/3/20175
13Mar3/4/20172
14Apr4/1/20179
15Apr4/2/20175
16Apr4/3/20172
17Apr4/4/20175
18May5/1/20173
19May5/2/20177
20May5/3/20173
21May5/4/20171
1 Solution

Accepted Solutions
sunny_talwar

I suggest using The As-Of Table

Table:

LOAD Transaction,

    Month,

    Date,

    Amount

FROM

[..\..\..\Downloads\Test Sum Load.xlsx]

(ooxml, embedded labels, table is Sheet1);

AsOfTable:

LOAD DISTINCT Date as AsOfDate,

Date(Date - IterNo() + 1) as Date

Resident Table

While IterNo() <= 3;

Capture.PNG

View solution in original post

3 Replies
devarasu07
Master II
Master II

Hi,

Try like below,

Create expression in your chart.

Aggr(RangeSum(Above(Sum(Amount), 0, RowNo())), Month, Date)


Capture.JPG

Thanks,Deva

sunny_talwar

I suggest using The As-Of Table

Table:

LOAD Transaction,

    Month,

    Date,

    Amount

FROM

[..\..\..\Downloads\Test Sum Load.xlsx]

(ooxml, embedded labels, table is Sheet1);

AsOfTable:

LOAD DISTINCT Date as AsOfDate,

Date(Date - IterNo() + 1) as Date

Resident Table

While IterNo() <= 3;

Capture.PNG

melissapluke
Partner - Creator
Partner - Creator
Author

That works perfectly, thank you!