Running column summary question

[#1]

I have 10+ year of daily records. All variables are binary (0/1).  However, I want to calculate on any given day what the sum of that  variable was the last 180 days.  So I would like to know what the sum was on January 1, 2021.  I also want to know what that is on January 2, 2021.  I know that 179 days are the same, but I want to have that calculated for every day.  What is the most efficient way to accomplish that?  Thank you.

[#2 · Accepted solution]

Hi,

If you don't mind creating an additional column, this will work and should be efficient: once the cumulative sum is computed, each cell is simply the difference of 2 cells in the cumulative sum column. You can accomplish the same thing (keeping the same efficiencies) with a self-referencing column formula and a conditional to handle the first 179 rows, but this is much more straightforward and should perform similarly.

1) Right-click on the column and select New Formula Column > Row > Cumulative Sum as in the image below.

2) Rename this column something short, like Cusum, for convenience in step 3.

3) Add a new column with this formula:

:cusum - Lag( :cusum, 180 )

Cheers,

Brady

[#3 · Accepted solution]

Here is another way to do this. This way does not require the intermediate column. I'm not sure which of the 2 methods I describe is more efficient... either one should perform decently.

The idea behind this one is to find an equally-weighted (that is what the 1 in the formula does) moving average of 180 terms (i.e., the current term and the 179 previous), then multiply by 180 to get the moving sum.

Cheers,

Brady

180 * Col Moving Average( :Values, 1, 179 )

[#4]

Thank you.  Both work