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!
1 -
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
Categories
- 10.6K All Categories
- APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 339 Workbench
- 251 Domo Best Practices
- 11 Domo Certification
- 461 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 62 Kowaza
- 295 仲間に相談
- 649 ひらめき共有