Count Distinct with Or Case Statement in Beast Mode

Cfunk

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!

Cfunk

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!

Godiepi

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

Cfunk

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.

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

Thanks!

Sean_Tully

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

@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(..)

Cfunk

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!