I am currently trying to gauge how many consistent event attendees are attending our upcoming show. For context, we have 2 events every year and every registration for an event is on a separate record which I have categorized by year between 2021-2024. I am trying to roll this up into a table that gives us a percentage of how many previous events they have attended and if they are attending 2024 or not. If they have attended 60% (3/5) or higher of our previous events, and have not registered for this one - we want to reach out and find out why.
I have the data that I need but I am not sure how to group my table my member ID.
This is what my raw data looks like and how I want my table to look.
However, I can't get the member IDs to group when I try to incorporate 2024 registrations. Normally I would add a distinct member id count beast mode and it groups them but when I add this beast mode:
CASE when Year like '%2024%' then 'Registered' else 'Not Registered' END
It creates a table like this:
Member ID | Member Name | Weight | Registered for this current show? |
|---|
ABC | Member 1 | 0% | Not Registered |
DEF | Member 2 | 0% | Not Registered |
JKL | Member 3 | 20% | Not Registered |
JKL | Member 3 | 0% | Registered |
MNO | Member 4 | 100% | Not Registered |
MNO | Member 4 | 0% | Registered |
STU | Member 5 | 80% | Not Registered |
STU | Member 5 | 0% | Registered |
QRS | Member 6 | 60% | Not Registered |
QRS | Member 6 | 0% | Registered |
How do I make it stop pulling up duplicate records to say they are registered for 2024?
