How do I create a 30 day rolling sum?

[Dave_Mosk]

My table has dates and IDs. I am trying to create a rolling 30 day total count for each ID. The goal is to identify instances of an ID coming up 4 or more times within any 30 day period. So it is not specific to an month, rather any 30 day period.

Attached is a dummy data file. For example, you can see ID 109 has more than one 4+ instances within separate 30 day rolling periods. I am also attaching a full calendar (I thought I would have to create that first and merge the data together to get full years because dates are missing where there is no ID associated with it.

Second question, is it possible to do this where it will ignore certain dates.

Thank you

[JamesCharnley · Accepted solution]

Hi @Dave_Mosk,

If I'm interpreting your question correctly then this should do what you want. It's using the multi-row formula tool, grouped by ID, to look forward 3 rows from a given date and see if that Row+3 date falls before 30 days after the date of the first row. If it does, it's tagging that row as 1 to indicate one instance of that ID coming up 4+ times within any 30 day period, otherwise it's 0. Then the IDs can be counted using a summarize tool.

In terms of the dates that you want to ignore it should be possible. What I'd expect is to outer join against a list of dates you don't want included and just apply the formula to those rows.

[gautiergodard]

@Dave_Mosk 

Would something like this solve your issue? 

Option 1 takes the last 30 days present in your data set and counts the number of IDs.

Option 2 calculates 30 days based on todays date and does the calculation that way.

Wasnt sure what you which one you needed!

Hope this helps.

[Dave_Mosk]

thank you. I am looking for how many times an ID appears within 30 days of that date in the data set.  For example, if I visited the supermarket on 10/1/21, 10/12/21, 10/25/21, and 11/16/21. So by 10/12 I visited twice looking back 30 days, on 10/25 I visited 3 times looking back 30 days, however my 4th visit was on 11/16 so I only visited twice looking back 30 days.

I am trying to identify which people and when did they visit the supermarket 4 or more times within a 30 day period, not from a 30 day subset or from the present date. 

[Dave_Mosk]

Thanks! Such a simple elegant solution.... love it!