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

• 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
```

**Make sure to any users posts that helped you.
• ⚪️

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!

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

**Make sure to any users posts that helped you.
• ⚪️

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.

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

**Make sure to any users posts that helped you.
• ⚪️

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

• Indiana 🥷

Hi @jmcgurl

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

**Did this solve your problem? Accept it as a solution!**
• Budapest / Portland, OR 🟤

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.

good beast mode but careful

``` -- this won't work
... `REGION CODE`= 'NA' AND `REGION CODE`= 'AP'

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"
• ⚪️

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.

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

**Make sure to any users posts that helped you.