Date list is before date

[Connor_Forsyth]

Hi there!

I’m looking to calculate the growth rate of attendees for events that I run.

Specifically, I’d like to understand how many attendees are first timers.

To spell out how this formula could work… I see x variables

1. Attendee
2. Attendee attended event date list
3. Event
4. Event dates

I’d like a sum of how many attendees attended this event if it was their first time (had not attended events prior.

if (
{Attendee} has NOT(BEFORE({attended event date list}, {Event dates}))

This is not really working…

One of the problems is that the rollup of events attended is just a list of dates, it’s not running through the formula one by one with the attendees.

Any advice on how this could work?

I have two linked databases with Attendees and events.

[JonathanB]

Not sure if I can fully answer this without seeing more of how your bases are set up, but you can use MIN() and MAX() in formulas to apply to dates, where this will give you the first and the most recent date, respectively. Does this help? Would need to see screenshots of the tables you are using to be able to assist further I think.

[Connor_Forsyth]

Hi Jonathan,

Thanks for your help! The MIN() formula certainly seems to be in the right direction.
However, it seems to not like it when the {linked field} is a string of dates.

I’ve used this formula:
MIN(
VALUE(DATETIME_FORMAT({Dates attended},‘DD-MM-YYYY’))
)

To understand what the minimum date is, however when {dates attended} is a string of dates, i.e. 12/11/21, 07/12/21, 28/01/22. It produces an error.

I’m going to attach some views of the database, would be curious to see your thoughts!

View of Attendees (The events they have attended)
View of Events (and the attendees that attended)

These are both linked together.

Cheers,
Connor.

[JonathanB]

I think maybe all you need is to use a rollup instead of a lookup for {First event}, like this:

That will give you the date of the first event each attendee attended. Is this what you need?