Business Days in the month
Comments
-
Hey @user04816, here's a statement that works in a MySQL dataflow that can pull all weekdays for a current month for a specific date. (does not account holidays).
(DATEDIFF(LAST_DAY(`Date`),`Date`) + 1) -
((WEEK(LAST_DAY(`Date`)) - WEEK(`Date`)) * 2) -
(case when weekday(`Date`) = 6 then 1 else 0 end) -
(case when weekday(LAST_DAY(`Date`)) = 5 then 1 else 0 end)You could then divide in a count(distinct(`date`)) where dayofweek(`date`) between 2 and 6 and `date` <= current_date().
This would theoretically get you a percentage of working days in the month used. (Not accounting for holidays). Let me know if this is helpful!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'1 -
Thank you but I'm not familiar with working with My SQL.
Just trying to create a super simple gauge chart ...
0 -
I'd use a Progress Bar card for this use case.
To accomplish this, you'd need to create three beast mode calculations - one for the total number of business days in the current month (to use as the max value) , second for the number of business days into the month we currently are, and a third that is just a simple title for the progress bar. The code will look a little scary because working with dates can be messy business, but the beast modes would look like this:
For Total Business Days in Month:
MAX(
DAY(LAST_DAY(CURRENT_DATE())) - (FLOOR(DAY(LAST_DAY(CURRENT_DATE())) / 7) * 2)
-
CASE
WHEN Mod(DAY(LAST_DAY(CURRENT_DATE())), 7) = ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) OR ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-01'), '%Y-%m-%d')) ) = 1 THEN 1
WHEN Mod(DAY(LAST_DAY(CURRENT_DATE())), 7) > ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) THEN 2
ELSE 0
END
)For Business Days into the Month:
MAX(
DAY(CURRENT_DATE()) - (FLOOR(DAY(CURRENT_DATE()) / 7) * 2)
-
CASE
WHEN Mod(DAY(CURRENT_DATE()), 7) = ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) OR ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), '%Y-%m-%d')) ) = 1 THEN 1
WHEN Mod(DAY(CURRENT_DATE()), 7) > ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) THEN 2
ELSE 0
END
)For Title (can be any text you like):
'How far along are we in the month?'
It's the same formula in both of the first two, just with a LAST_DAY function added to the Total business days version in order to do the calculation for the last day of the month.
Once you've built these beast mode calculations, just add them to the Progress Bar card with Title as GAUGE NAME, Business Days into the Month as GAUGE VALUE, and Total Business Days in Month as MAX VALUE.
I hope this helps!
2 -
Wow - thank you!!! This worked perfectly!
Could you pleasee help me tweak it to be days in the month? (not just business days but total days?)
0 -
Total days is much, much simpler.
For Total Days in the month:
MAX(DAY(LAST_DAY(CURRENT_DATE())))
For Days into the month:
MAX(DAY(CURRENT_DATE()))
0 -
THANK YOU!!!!! ?
0 -
@lb1234 - It is a pretty complex calculation, isn't it. I wish it were a little easier to read for you.
The calculation is subtracting 2 days for each week in the month to account for Saturdays and Sundays. I believe the equation should work if you were to change the "2"s to "1"s. You could also eliminate the "* 2" part of the equation instead of changing that one to "* 1" for simplicity sake.
Give that a try and let me know if it gives you want you're looking for.
MAX(
DAY(LAST_DAY(CURRENT_DATE())) - (FLOOR(DAY(LAST_DAY(CURRENT_DATE())) / 7) )
-
CASE
WHEN Mod(DAY(LAST_DAY(CURRENT_DATE())), 7) = ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) OR ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-01'), '%Y-%m-%d')) ) = 1 THEN 1
WHEN Mod(DAY(LAST_DAY(CURRENT_DATE())), 7) > ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) THEN 1
ELSE 0
END
)
0 -
You could always do it in a few Magic tiles if you prefer to stay out of MySQL. The calendar dimensions dataset comes in handy all the time. Narrow down the two populations that you're interested in with their own filter path, group by a constant (like _BATCH_ID_) to count dt, then join them back up on your constant. Let's you adjust the weekdays that you care about, whether you want "today" included, or any other variants (like holiday exclusions with the isHoliday flag) WITHOUT diving into the finer points of MOD() functions :)
2 -
@lb1234 - Unfortunately, I've not created this type of calculation for other time frames.
Calculating the start and end days for the quarter version could be pretty complex. I would definitely look at an ETL/join-based solution for that.
For the year calculation, you know the total will either be 313 or 312 because there will always be either 52 weeks in a year or, if Jan 1 is a Sunday, 53, or if it is a leap year and Jan 1 or Jan 2 is a Sunday, then 53.
Your numerator would be that number minus how many Sundays have passed (number of days into the year [ DAYOFYEAR(CURRENT_DATE() ] divided by 7). Probably some more details in there with the starting day of the year to work out as well... Also complex enough that it might be best to look at an ETL solution.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive