how to calculate moving sum in the table

[ShaoChi]

If I have data like this:

ShoppingDate	consumer	item
1/1	A	1
1/3	A	2
1/5	A	1
1/15	A	2
1/20	A	5
1/2	B	4
1/3	B	1
1/10	B	2
1/11	B	1

I want to calculate the sum of purchases made by each consumer in 3 days, like this

ShoppingDate	consumer	moving sum
1/1	A	1
1/2	B	4
1/3	A	3
1/3	B	5
1/5	A	3
1/10	B	2
1/11	B	3
1/15	A	2
1/20	A	5

How to use "Chart General Numeric Functions" to get the field "moving sum" value ??

[edwin]

try this:

[edwin]

you it will be

aggr(rangesum( above( sum(Measure),0,RowNo())), consumer,shopDate)

there are a lot of samples:

https://community.qlik.com/t5/New-to-Qlik-Sense/Rangesum-cumulative-with-2-dimensions/td-p/1285355

[ShaoChi]

NO, this answer is wrong...

[edwin]

the screen capture is my data not from yours

[edwin]

the proposed solution is the expression and not the data

[edwin]

try this as well:

=rangesum(above(sum(item),0,rowno())),

you have to have Consumer as 1st column and date as 2nd column

[edwin]

use this as ref:

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Range...

[ShaoChi]

Thank you for your patience reply, the above sample almost solved my problem.

I use this formula: Aggr(RangeSum(Above(Count(distinct [ShoppingDate]), 1, 29)), [consumer], ( [ShoppingDate] , (NUMERIC, ASCENDING)) )

Calculation results:

consumer	ShoppingDate	= Aggr(RangeSum(Above(Count(distinct [ShoppingDate]), 1, 29)), [consumer], ( [ShoppingDate] , (NUMERIC, ASCENDING)) )
A	2021/3/2	0
A	2021/6/20	1
A	2021/7/26	2
A	2021/9/8	3
A	2021/10/20	4
A	2021/10/23	5
A	2021/12/16	6
A	2022/1/17	7
A	2022/2/23	8
A	2022/2/24	9
A	2022/3/13	10
B	2021/11/18	0
B	2022/1/31	1
B	2022/2/1	2

There is still a problem: The rolling 30 days I need to calculate are 30 consecutive days, but the ShoppingDate is not 30 consecutive days, how can I adjust it to calculate the 30 days of the calendar day?

[ShaoChi]

Thank you for your patience reply, the above sample almost solved my problem.

I use this formula: Aggr(RangeSum(Above(Count(distinct [ShoppingDate]), 1, 29)), [consumer], ( [ShoppingDate] , (NUMERIC, ASCENDING)) )

Calculation results:

There is still a problem:

The rolling 30 days I need to calculate are 30 consecutive days, but the ShoppingDate is not 30 consecutive days, how can I adjust it to calculate the 30 days of the calendar day?