Sum data based on several criteria

jmcgurl
jmcgurl Member
in Charting

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 Coach

    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


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jmcgurl
    jmcgurl Member

    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 Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jmcgurl
    jmcgurl Member

    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 Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jmcgurl
    jmcgurl Member

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

  • GrantSmith
    GrantSmith Coach

    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)


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jmcgurl
    jmcgurl Member

    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 Coach

    @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`)


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jmcgurl
    jmcgurl Member

    @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 Coach

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories