Identify percent of people attending current event

deona720
deona720 Member
in Charting

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.

DOMO Example.xlsx

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?

Tagged:

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    You need to give your "registered" vs. "not registered" column a way to aggregate, otherwise it doesn't know how to collapse the different values. I think this should work (it's basically just wrapping your beastmode in a SUM and another CASE statement)

    case
    when sum(case when Year like '%2024%' then 1 else 0 end) > 0
    then 'Registered'
    Else 'Not Registered'
    END

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    You need to give your "registered" vs. "not registered" column a way to aggregate, otherwise it doesn't know how to collapse the different values. I think this should work (it's basically just wrapping your beastmode in a SUM and another CASE statement)

    case
    when sum(case when Year like '%2024%' then 1 else 0 end) > 0
    then 'Registered'
    Else 'Not Registered'
    END

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • deona720
    deona720 Member

    @DavidChurchman That worked perfectly, thank you so much!!

Categories