390533_en-US

liliconcom

Running column summary question

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.

Data TableScriptingRe: Running column summary question

Thank you.  Both work

Re: Running column summary question

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

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