topic Re: Cumulative sum by grouping and rolling values formula?? in Discussions

Cumulative sum by grouping and rolling values formula??

chadyoder — Sat, 10 Jun 2023 23:55:56 GMT

I have a data set with groups, days and visits on those days. I would like to get a rolling sum of every 3 days, so at day 3 the value would be sum of day 1, 2, 3, but on day 4 the value is the sum of days 2, 3,4. And do this by group. I know I can use the cumulative sum by group, but that returns the sum each day, so day 4, would be the sum of all 4 days, but I would like the first day to drop off at that point. Any help would be great.

Group	Day	Visits
1	1	0
1	2	0
1	3	9
1	4	10
1	5	14
1	6	22
1	7	11
1	8	12
2	1	2
2	2	4
2	3	1
2	4	3
2	5	2
2	6	2
2	7	3
2	8	1

Re: Cumulative sum by grouping and rolling values formula??

jthi — Tue, 25 Oct 2022 19:08:50 GMT

One option would be to use Col Moving Average and multiply that with the amount of days

Col Moving Average(:Visits, 1, 2, 0, 1, :Group) * 3

Re: Cumulative sum by grouping and rolling values formula??

txnelson — Tue, 25 Oct 2022 19:20:24 GMT

Here is a one way of handling it

avg = Col Moving Average( :Visits, weighting = 1, before = 2, :Group );
count = Col Cumulative Sum( 1, :Group );
If( count >= 3, avg * 3, . );

@jthi has the simpler option.....I assumed Col Moving Average gave responses when n was less than 3.