Count Distinct with Or Case Statement in Beast Mode

Options
Cfunk
Cfunk Member
in Beast Mode

Hi! Happy Monday!

I'm trying to create a beast mode in which I'm counting the distinct sum of registration IDs regardless of the number of products that are attached to that registration ID except when the product name contains 'Guest.'

So if a person registers for multiple sessions all of those are under the same registration ID and should be counted as one. Except their guest should count as a second registrant count.

I included a chart to illustrate what I mean:

Registration ID

Guest/Spouse Registration

Leaders

Registration

Women and Allies

Mutual Community

Total Needed

1234

1

1

1

2

1235

1

1

1

1

1

1236

2

1

3

1237

1

1

1

1

2

I've tried a few different formulas but keep getting a formula error. I placed the formulas I tried below:

case when Product Name= 'Leaders' or (Product Name='Registration') or (Product Name='Women and Allies ') OR (Product Name= 'Mutual Community') then COUNT(DISTINCT Registration Id) ELSE
case when Product Name= 'Guest/Spouse Registration' then COUNT(Registration Id) END

case when Product Name= 'Leaders' then COUNT(DISTINCT Registration Id) ELSE
when Product Name= 'Guest/Spouse Registration' then COUNT(Registration Id) END

COUNT(DISTINCT case when Product Name not like '%Guest%' then Registration Id else COUNT(Product Name)end)

Thank you in advance!

Tagged:

Best Answer

  • Cfunk
    Cfunk Member
    Answer ✓
    Options

    Sorry, I tried to mask the data a little at first, but I did update your formula with the correct fields.

    I tweaked the formula a bit and I got the result I expected:

    IFNULL(COUNT(CASE WHEN Product Name = 'Washington Summit - Guest/Spouse Registration' THEN Registration ID END),0)
    +
    IFNULL(COUNT(distinct Registration Id),0)

    Thank you so much for your help!

Answers

  • Godiepi
    Godiepi Coach
    Options

    Hello @Cfunk

    Your beastmode is probably easier than you think. Im not sure how is your data structure but if it was like the "Data Sample" in the below screenshot , you could do the formula as follows:

    IFNULL(COUNT(CASE WHEN Product Name = 'Guest/Spouse Registration' THEN Registration ID  END),0)
    
+
    
IFNULL(COUNT(DISTINCT CASE WHEN Product Name <> 'Guest/Spouse Registration' THEN Registration ID  END ),0)

    The above beastmode is represented as the "Test Cnt" value in the screenshot of the pivot table

    image.png

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • Cfunk
    Cfunk Member
    Options

    Hi,

    Thank you for your help!!!! It didn't quite give me what I would expect but maybe I didn't explain it well.

    So what I would expect here is that the total registration would be 191. So, several of the product names will all be attached to one registration ID and one attendee. However, the guest adds a second attendee despite sharing the same registration ID.

    We're looking for the total no-show attendees. A registration id can be attached to all of the product names, however, it's all one attendee; EXCEPT when they have a guest. So the formula would need a distinct count of the registrant id using all the product names and then a distinct count of the product name of guests.

    The first column uses a count distinct with registration ID, the second uses the formula provided @godiepi and we're getting the same amount.

    image.png

    I hope that makes more sense. Please let me know if I can clarify any further.

    Thanks!

  • Sean_Tully
    Sean_Tully Contributor
    Options

    I'm not sure this is possible in beast mode, since you want to both count distinct values of a column and also dedupe an already aggregated column? I think you might have to use ETL to create IDs for the guests/spouses so you can count them correctly. That's just my two cents, though, hopefully someone else has an easier solution!

  • Godiepi
    Godiepi Coach
    Options

    @Cfunk

    Did you adjust the beastmode proposed ? or are you still using the exact code… Im asking because I see your Product Name value = 'Washington Summit - Guest/Spouse Registration' instead of just 'Guest/Spouse Registration'

    you will need to either match the exact wording or adjust the product name within the beastmode to take only in consideration whats after the dash 'Guest/Spouse Registration' …

    this way you will COUNT(DISTINCT .. )everything except for when the product name is the guest registration to do a COUNT(..)

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • Cfunk
    Cfunk Member
    Answer ✓
    Options

    Sorry, I tried to mask the data a little at first, but I did update your formula with the correct fields.

    I tweaked the formula a bit and I got the result I expected:

    IFNULL(COUNT(CASE WHEN Product Name = 'Washington Summit - Guest/Spouse Registration' THEN Registration ID END),0)
    +
    IFNULL(COUNT(distinct Registration Id),0)

    Thank you so much for your help!

Categories