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.


Current Output: 

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 SmithHot Dog1
Virginia John SmithHamburger 2
Virginia John SmithPizza3
Virginia John SmithChicken Wing4
Virginia John SmithPizza5
North Carolina Iron ManHot Dog1
North Carolina The Doc Pizza1
North Carolina The Doc Chicken Wing2
North Carolina The Doc sushi3
North Carolina The Doc Hot Dog4
TexasTim Tatty Hot Dog 1
TexasTim Tatty Fruit2
TexasTim Tatty Beef3
TexasRed hat Pizza 1
TexasRed hat Hot Dog 2
TexasBlue BoyPizza 1
TexasMark DemarkHot Dog 1
Texas Mark MallBurgPizza1
TexasMat HammondPizza1


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

select *

from master 

where lunch = 'pizza'


create table oneb as 

select distinct 


count(distinct guest) as pizza count

from one 

group by 1 


create table two as 

select distinct *

from master 

where guest in ( select distinct guest from one)

and lunch = 'Hot Dog' 


create table twob as 

select distinct state,

count(distinct guest)

from two

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. 



  • NewsomSolutions

    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 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. - matt

  • user05225



    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. 


    Virginia John SmithHot Dog1 1
    Virginia John SmithHamburger 2  
    Virginia John SmithPizza3  
    Virginia John SmithChicken Wing4  
    Virginia John SmithPizza51 
    North Carolina Iron ManHot Dog1 1
    North Carolina The Doc Pizza11 
    North Carolina The Doc Chicken Wing2  
    North Carolina The Doc sushi3  
    North Carolina The Doc Hot Dog4 1
    TexasTim Tatty Hot Dog 1 1
    TexasTim Tatty Fruit2  
    TexasTim Tatty Beef3  
    TexasRed hat Pizza 11 
    TexasRed hat Hot Dog 2 1
    TexasBlue BoyPizza 11 
    TexasMark DemarkHot Dog 1 1
    Texas Mark MallBurgPizza11 
    TexasMat HammondPizza11 


    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.