Count Distinct with Or Case Statement 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!
Best Answer
-
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' THENRegistration ID
END),0)
+
IFNULL(COUNT(distinctRegistration Id
),0)Thank you so much for your help!
1
Answers
-
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
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'0 -
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!
0 -
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!
1 -
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'1 -
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' THENRegistration ID
END),0)
+
IFNULL(COUNT(distinctRegistration Id
),0)Thank you so much for your help!
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive