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.
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
