Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
melissapluke
New Contributor III

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

Re: Rolling Sum

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

3 Replies
devarasu07
Honored Contributor II

Re: Rolling Sum

Hi,

Try like below,

Create expression in your chart.

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


Capture.JPG

Thanks,Deva

Re: Rolling Sum

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

Re: Rolling Sum

That works perfectly, thank you!

Community Browser