Sum data based on several criteria

Hi All, I am new to Domo and trying to figure out how I can create a specific card looking at a few columns of my data. Overall, I am trying to search a particular day of the week (sunday in this case), then search for a certain criteria on that day, then add up the unique count. However, I am not sure if a beast mode calculation is the way to go with that. Here is what i tried

IF weekday(1)If WEEKDAY(1) and `SESSION PURPOSE` = 'FULL_STORE_SCAN' and `REGION CODE`= 'NA' and `REGION CODE`= 'AP' then COUNT(DISTINCT `STORE NO`) 

I have also tried this as well: 

case 

when WEEKDAY(1)

 when `SESSION PURPOSE` = 'FULL_STORE_SCAN'

when `REGION CODE`= 'NA'

Then COUNT(DISTINCT `STORE NO`)

End

Neither is a successful calculation unfortunately. Thanks in advance for any help / suggestions / tips. I really appreciate it. 

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Your case statement is close, but you need to use AND to include multiple criteria, like this:

    case 
    
    when WEEKDAY(1) AND  `SESSION PURPOSE` = 'FULL_STORE_SCAN' AND `REGION CODE`= 'NA'
    
    Then COUNT(DISTINCT `STORE NO`)
    
    End
    


  • jmcgurl
    jmcgurl ⚪️

    Hi Mark,

    Thank you very much for this input. It seems to work much better. However, a follow up question is that I realized that my data set has just the date (ex 7/3/21, 7/23/21 etc) and not the day of the week. Do I need to add an extra column to the data set to determine the day of the week to be able to run this case successfully? Also, if I want to add another region to this this beast mode would it be something like:

    case 
    
    when WEEKDAY(1) AND  `SESSION PURPOSE` = 'FULL_STORE_SCAN' AND `REGION CODE`= 'NA' AND `REGION CODE`= 'AP' 
    
    Then COUNT(DISTINCT `STORE NO`)
    
    End
    

    Thanks again for all the help!

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Sorry, that slipped by me the first time. To get Sunday from the date, you just need to do this:

    WHEN WEEKDAY('datefield') = 1 AND  `SESSION PURPOSE` = 'FULL_STORE_SCAN' AND `REGION CODE`= 'NA'
    
    Then COUNT(DISTINCT `STORE NO`)
    
    End
    

    Replace datefield with the actual name of the field that has the date.

  • jmcgurl
    jmcgurl ⚪️

    Thanks! For some reason I dont seem to get any result when trying to run this beastmode for the single number car. I will keep playing trying a few things this morning to see if I can get some data to be returned.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    When troubleshooting, I try and break things down and also use a table card to easily see the results. I would try making 3 separate beast modes, one for each criteria in your beast mode and then put each of them in your table card. Something like this:

    Weekday beast mode field

    CASE WHEN WEEKDAY('datefield') = 1 THEN 'Y' ELSE 'N' END
    

    Session purpose beast mode field

    CASE WHEN `SESSION PURPOSE` = 'FULL_STORE_SCAN' THEN 'Y' ELSE 'N' END
    

    Region beast mode field

    CASE WHEN `REGION CODE`= 'NA' THEN 'Y' ELSE 'N' END
    

    Drag your store no field and these 3 beast mode fields into a table card. If you see a row that has Y for all 3 beast mode fields, then your syntax is correct. If not, then you need to look at your data and see what values are actually being produced.

  • jmcgurl
    jmcgurl ⚪️

    Cool Thank you so much! I will try these today.

  • GrantSmith
    GrantSmith Indiana 🔴

    Hi @jmcgurl

    You'll want to have your case statement inside your aggregate:

    COUNT(DISTINCT case 
    
    whenate WEEKDAY(`datefield`) = 1 AND  `SESSION PURPOSE` = 'FULL_STORE_SCAN' AND `REGION CODE`= 'NA' AND `REGION CODE`= 'AP' 
    
    Then `STORE NO`
    
    End)
    


  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @jmcgurl ,

    why are you hard coding all these filters into your beast modes? would it not make sense to use Quick Filters or Filter Cards to pass Region filter context to your viz? (this allows you to avoid single use beast modes.


    @GrantSmith

    good beast mode but careful

     -- this won't work
     ... `REGION CODE`= 'NA' AND `REGION CODE`= 'AP'
    
     -- instead
    REGION CODE`IN ( 'NA', 'AP' )
    


    Also @jmcgurl , be careful with the weekday function, just like excel sometimes the default for 1 is a sunday, other times it's a Monday. check and be sure.

  • jmcgurl
    jmcgurl ⚪️

    Thank @GrantSmith & @jaeW_at_Onyx for the other tips. Unfortunately I ran out of time yesterday to keep working on it but plan to do more today.

    @jaeW_at_Onyx - I have not considered using just filters but taking quick look at them I am not sure how I would be able to just look for every Sunday across the data set. I would assume I would need to change that to weekly to capture the weeks data.

    Sorry still new to domo and trying to figure out all the cool things that can be done with the data.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    @jmcgurl to @jaeW_at_Onyx 's suggestion that I totally agree with, you can create beast mode to just return the day of the week and bring that into your quick filters. You could do this:

    WEEKDAY('datefield') or you could use DAYNAME('datefield') which would Sunday, Monday, etc, which would be more readable to people than 1,2,3...

    Add your session scope and region code fields to the quick filter and then your beast mode becomes much simpler and would just be:

    COUNT(DISTINCT `STORE NO`)
    


  • jmcgurl
    jmcgurl ⚪️

    @MarkSnodgrass For the DAYNAME('datefield') calculation, I do not get anything returned. I checked my data set and see that the column is titled date. So I also tried DAYNAME('date') to see if that might have been the issue but still do not get anything returned. Does it matter that in the raw data the date is setup as mm/dd/yyyy? I would imagine that is the column that is being looked at to determine the day of the week.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    @jmcgurl you need to replace 'datefield' with the actual date field that is in your dataset. That was just a placeholder since I didn't know the name of your field.