Loop to count when rows meet a certain criteria

[willd9]

I have a dataset that has an Event Name and an Event Date. What I want to do, is to be able to count how many Events are within the next 3 months by row. So here is an example of the data:

Event_Name	Event_Date
Event1	17/03/2021
Event6	05/04/2021
Event35	06/04/2021
Event78	09/02/2020

What I want is to loop through every record to see if any of the other Events are within the next 3 months, if they are, then it adds 1 to a count. The desired output would then look like this:

Event_Name	Event_Date	Upcoming_Events
Event1	17/03/2021	2
Event6	05/04/2021	1
Event35	06/04/2021	0
Event78	09/02/2020	0

Any ideas?

[FinnCharlton · Accepted solution]

Hi @willd9 , this batch macro will do that for you. In short, it inputs the dates one by one, and for each one checks how many other dates are within 3 months. Hope it helps!

[StellaBon]

You would use a formula tool, create a new column "3 Out". Then use your DateTime functions, DatetimeToday gets whatever the current date is. Wrap DatetimeAdd around that : DateTimeAdd(DateTimeToday(), 3, "Months")

Then with Formula you create a column called "Count Events" and say: if Event Date > Datetime today() and < [3 Out] then "Count"

Else "Do Not" Endif. Then you use Summarize to Count  instances of "Count" in column "Count Events".

[RobertOdera]

Hi, @willd9 

This sounds like a use case for a macro (iterative qualifying three-month window?) or batch macro.

Still, I will offer the following:

1. an N-days window might be better than Months-window. For example, three months from the first of the month might differ from three months from the last for any given month. A N-days window might be more precise, e.g., 90 days from Today() date = "within the next 90 days."

2. below is a brute force step-through (but I'm uncertain if it will remain solid for a larger dataset)

I hope you find it helpful.

[willd9]

When I've attempted to use the macro on my actual input file (970 rows), the macro outputs 5890 rows... I can't imagine this is right! Any ideas why it's doing this?

[willd9]

When I've attempted to use the macro on my actual input file (970 rows), the macro outputs 5890 rows... I can't imagine this is right! Any ideas why it's doing this?

[FinnCharlton]

@willd9 do some of your events have the same name? If so, map the 'Event Name' field in the batch macro configuration to a unique identifier for each event.