Beast Mode With Two Dependent Criteria
New to Domo but I love it so far. I am trying to create a data table that will let me find people that have fulfilled two criteria.
Dynamic Classification Variable || Distinct count of guests that had pizza || Distinct count of guests that have had a hot dog.
Virginia || 1 || 1
North Carolina || 1 || 2
Texas || 4 || 3
I've attempted to accomplish this using two case statements.
count( distinct Case when `lunch` = 'Pizza' then `guest` end )
count( distinct Case when `lunch` = 'Hot Dog' then `guest` end )
Both of these work fine but they do not account for a rule I'll need to put in place that will limit the second set to only those that have met the first criteria. I only want to count guests that ate Pizza and a hot dog.
Here is an example of my data:
|Virginia||John Smith||Hot Dog||1|
|Virginia||John Smith||Chicken Wing||4|
|North Carolina||Iron Man||Hot Dog||1|
|North Carolina||The Doc||Pizza||1|
|North Carolina||The Doc||Chicken Wing||2|
|North Carolina||The Doc||sushi||3|
|North Carolina||The Doc||Hot Dog||4|
|Texas||Tim Tatty||Hot Dog||1|
|Texas||Red hat||Hot Dog||2|
|Texas||Mark Demark||Hot Dog||1|
Historicly I would accomplish this by creating two tables and using a left join onto a table of just the raw values.
create table one as
where lunch = 'pizza'
create table oneb as
count(distinct guest) as pizza count
group by 1
create table two as
select distinct *
where guest in ( select distinct guest from one)
and lunch = 'Hot Dog'
create table twob as
select distinct state,
group by one
left join oneb and twob to get the desired output.
Unfortunatly I don't have access to ETL's so I need to do it in a beastmode. Any help would be appreciated.
It's late and I may not have read through everything correctly but follow me here. If you don't have access to ETL and your beast modes are only doing one peice to a two piece calculation. Why don't you just leave two BM...one for a flag for 'pizza' and one a flag for 'hot dog'...then do a filter on your card for those that have only those two flags? That may be a very over simplistic way to solve this...but wanted to throw it out. - matt0
I messed around with the idea this morning and didn't have any luck. I beleive the issue is steming from the structure of my data. When I added the two filters and selected the first option the second filter would no longer populate values.
I envision the structure of the data looking like this after the beastmode is executed because my hotdog count will always drop to zero once I filter for pizza.
State Guest Lunch Day BeastMode1_PIZZA BeastMode2_Hotdog Virginia John Smith Hot Dog 1 1 Virginia John Smith Hamburger 2 Virginia John Smith Pizza 3 Virginia John Smith Chicken Wing 4 Virginia John Smith Pizza 5 1 North Carolina Iron Man Hot Dog 1 1 North Carolina The Doc Pizza 1 1 North Carolina The Doc Chicken Wing 2 North Carolina The Doc sushi 3 North Carolina The Doc Hot Dog 4 1 Texas Tim Tatty Hot Dog 1 1 Texas Tim Tatty Fruit 2 Texas Tim Tatty Beef 3 Texas Red hat Pizza 1 1 Texas Red hat Hot Dog 2 1 Texas Blue Boy Pizza 1 1 Texas Mark Demark Hot Dog 1 1 Texas Mark MallBurg Pizza 1 1 Texas Mat Hammond Pizza 1 1
I think a work around would be the ability to filter a field based on the output of a beastmode but I have not found a way to do this yet.
By pulling all observations for guests with pizza, not just the obs where it's populted, the filter would work.0
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 80 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive