Business Days in the month

Is there a way to create a gauge chart just showing the percentage of where we are 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'
  • Thank you but I'm not familiar with working with My SQL.

     

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

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

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

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

     

  • THANK YOU!!!!! ? 

  • @zcameron I'm trying to work through your total business days in the month calculation but I'm having some difficulty figuring out how I can include Saturdays in this calculation. Is this possible?

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

     )

  • @zcameron is it also possible to get this same formula but for days in the current week, quarter and year?

  • Billobi
    Billobi Contributor

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



  • @zcameron that worked perfectly, thank you! Now I just need to figure out how to do this for current quarter and current year.

  • @Billobi I contemplated using this in the ETL for the dataset that I'm using but I wanted a way to be able to calculate this on the card level for different date columns rather having to create these joins for each date column that I may want to use it on.

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

  • @zcameron thank you for your help!