Cumulative sum by grouping and rolling values formula??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 |
Data Blending and CleanupQuality and Process EngineeringRe: Cumulative sum by grouping and rolling values formula??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??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.
