topic Running column summary question in Discussions

Running column summary question

chadyoder — Sat, 10 Jun 2023 23:31:09 GMT

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.

Re: Running column summary question

brady_brady — Fri, 04 Jun 2021 17:31:04 GMT

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

Re: Running column summary question

brady_brady — Fri, 04 Jun 2021 19:21:45 GMT

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 )

Re: Running column summary question

chadyoder — Fri, 04 Jun 2021 19:58:47 GMT

Thank you. Both work