How do I create a 30 day rolling sum?

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

dummy data.xlsx

Calendar.xlsx

Developer

Transformation

Accepted answers

JamesCharnley

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.

Row+3 Zipped.yxzp

All comments

JamesCharnley

Hi @Dave_Mosk,

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.

Row+3 Zipped.yxzp

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.

30daysummary.yxmd

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!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335