Count the number of times one day appears in a month
Answers
-
Probably need a little more context as to what you are trying to do, but generally you could do this:
In your card, set the date range filter to Previous Month
Create a beast mode that uses the DAYNAME() function to return the name of the day of a given date.
Drag that beast mode into your card and then drag it again and set the aggregate function to Count.
This assumes you only have one row per date.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
I have some values for the whole month, and need to find the values for an average Monday, so I added all the values but now I need to divide those values by the number of Mondays. I have tried using average but the values are wrong, the only way to get the values I am looking for is to divide those values by the number of Mondays in that month, its there anyway I can create a Beastmode formula that will return the number of Mondays in the previous month?
0 -
Yes, there are multiple Mondays in the data though so for Monday, 5/3 there are 5 rows with the same date but different data in the other columns
0 -
Use a CASE statement. CASE when <dayOfWeek> = 'Monday' then 1 else 0 end.
You could alternatively do crazy math where you do the Number of days in a month %% 7 and then do math on the remainder to figure out if the remainder would include another Monday.
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"0 -
This will give you the dominator
CASE
WHEN DAYNAME(`Date`) = 'Sunday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))
WHEN DAYNAME(`Date`) = 'Monday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))
WHEN DAYNAME(`Date`) = 'Tuesday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))
WHEN DAYNAME(`Date`) = 'Wednesday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))
WHEN DAYNAME(`Date`) = 'Thursday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))
WHEN DAYNAME(`Date`) = 'Friday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))
WHEN DAYNAME(`Date`) = 'Saturday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))
END
0 -
@DataUp , your beast mode will not behave as expected UNLESS you have the day of the week (Sun, Mon Tues) on the axis. At which point your CASE statement is superfluous and you could just do a count(distinct on dates).
Even that aside, conceptually you're mixing up two things. In your solution, you've combined what should be two separate beast modes into one.
Your CASE statement should be used to define a column (the name of the day of the week) which you can display as an axis on your report. then the COUNT DISTINCT should be used as the metric.
Imagine what'd happen if you did not have the name of the day of the week on the axis. In one row you'd have ALL the days of the month. So then when your beast mode evaluates for the days that are a Monday you get 5, for the days that are Tuesday you'd get 4 and wednesday a 4... so then what should analyzer do with those results? SUM them up? you'd end up with 31 :P
Last best practices thing. be careful to avoid nesting beast modes inside a CASE statement. keep in mind, if you perform a CASE statement outside the aggregation (your COUNT DISTINCT) then theoretically the aggregation happens BEFORE the CASE statement has been evaluatated. In other words, you'll calculate Count distinct before you've tested what day of the week it is. (at which point you might be thinking ... 'but i evaluated the date inside the count disticnt, which returns us to my initial statement that the CASE statementt is currently superfluous.)
hope that makes sense.
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"0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive