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_NameEvent_DateEvent117/03/2021Event605/04/2021Event3506/04/2021Event7809/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_NameEvent_DateUpcoming_EventsEvent117/03/20212Event605/04/20211Event3506/04/20210Event7809/02/20200 Any ideas?

Loop to count when rows meet a certain criteria

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?

Preparation

Accepted answers

FinnCharlton

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!

Date count batch.yxzp

All comments

FinnCharlton

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!

Date count batch.yxzp

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.

For_willd9.yxzp

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703