Business Days in the month

user04816

Is there a way to create a gauge chart just showing the percentage of where we are in the month?

 

 

PodiumMason

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!

 

user04816

Thank you but I'm not familiar with working with My SQL.

 

Just trying to create a super simple gauge chart ...

zcameron

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!

user04816

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

zcameron

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

 

user04816

THANK YOU!!!!! ?