Sum data based on several criteria

jmcgurl

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. 

MarkSnodgrass

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

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

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

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

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

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

GrantSmith

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

@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

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

@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

@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

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