Number of business days in the month

user060355 · 2024-10-07T21:52:30+00:00

There was an error rendering this rich post.

I am trying to write a beast mode that given a date can tell me the number of business days there are in that month. My company's hours are a little different so I want it to count days according to these rules:

Sun: 0, Mon: 0.5, Tues-Fri: 1, Sat: 0.5

I can't seem to figure out how to loop through to do this calculation so any help is greatly appreciated

Find more posts tagged with

Date

Beast Mode

Accepted answers

Manasi_Panov

https://community-forums.domo.com/main/discussion/comment/98750#Comment_98750

Hello @user060355

Yes, you can use the Domo Dimensions Connector and functions like CURRENT_DATE(), MONTHNAME(), and FIXED BY to calculate by month and identify the current month.

All comments

Manasi_Panov

Hi @user060355,

You can create a CASE statement in Beast mode (my 'date' colum name is 'submitted at'):

CASE
WHEN DAYOFWEEK(submitted at) = 1 OR DAYOFWEEK(submitted at) = 7 THEN 0
WHEN DAYOFWEEK(submitted at) = 2 THEN 0.5
WHEN DAYOFWEEK(submitted at) >2 AND DAYOFWEEK(submitted at) < 7 THEN 1
END

Here are the results, enhanced with additional columns for improved visualization:

You can then apply a filter to calculate a single month, etc.

user060355

The thing is that I need the value of the equation to equal the entire month. Provided today's date, I need it to calculate the number of business days in all of October. In my data, a single row is a month, so I would need to calc to understand that it's October and be able to tell me how many days there are in October according to my rules.

TimD

We use a Dataset that contains all days and date information to 2099. I believe we originally got this table from Domo. You can query this table any which way.

It is really quite helpful for looking up all types of date/day/month/year/workday/… info!

Period Over Period Lookup Table

Columns:

DimDate
Year
Quarter
Month
Day
DayOfWeek
MonthName
DayName
WeekNumber
IsWeekDay
IsWeekend
Today
Yesterday
Last7Days
Last30Days
Last60Days
Last90Days
Last3Months
Last6Months
Last12Months
IsEndOfMonth
IsHoliday
HolidayDescr
CurrentYear
PreviousYear
CurrentMonth
PreviousMonth
PreviousYearCurrentMonth
CurrentWeek
PreviousWeek
PreviousYearCurrentWeek
CurrentYearYTD
PreviousYearYTD
CurrentMonthMTD
PreviousMonthMTD
PreviousYearCurrentMonthMTD
CurrentWeekWTD
PreviousWeekWTD
PreviousYearCurrentWeekWTD
Date_YOY
Date_YOY_Slice
Date_YOY_YearsFromCurrent
Date_MOM
Date_MOM_Slice
Date_MOM_MonthsFromCurrent
Date_QOQ
Date_QOQ_Slice
Date_QOQ_QuartersFromCurrent
Date_WOW
Date_WOW_Slice
Date_WOW_WeeksFromCurrent

user060355

https://community-forums.domo.com/main/discussion/comment/98753#Comment_98753

@TimD that sounds great. Can you post the spreadsheet?

TimD

This is a large file = 73K. Try reaching out to Domo Support for a copy first. I will also need to clear if I can post that data (if it comes to that).

TimD

Just found this reference in Domo:

https://community-forums.domo.com/main/discussion/61254/dimensions-calendar-update

TimD

Sorry about another update. Looks like it is a Domo Connector!!!

Domo Dimensions Connector

Manasi_Panov

https://community-forums.domo.com/main/discussion/comment/98750#Comment_98750

Hello @user060355

Yes, you can use the Domo Dimensions Connector and functions like CURRENT_DATE(), MONTHNAME(), and FIXED BY to calculate by month and identify the current month.

ggenovese

You can do this in a beast mode by starting with the number of days in the month then subtract the number of sundays and half of the saturdays and mondays:

-- calculate the number of business days in month given a date (`dt`) in that month 
-- start with # of days in month
day(LAST_DAY(`dt`))

-- subtract half of the saturdays
- (FLOOR((WEEKDAY(DATE(DATE_FORMAT(`dt`,'%Y-%m-01'))-0)+LAST_DAY(`dt`)-DATE(DATE_FORMAT(`dt`,'%Y-%m-01')))/7) / 2)

-- subtract sundays
- FLOOR((WEEKDAY(DATE(DATE_FORMAT(`dt`,'%Y-%m-01'))-1)+LAST_DAY(`dt`)-DATE(DATE_FORMAT(`dt`,'%Y-%m-01')))/7)

-- subtract half of the mondays
- (FLOOR((WEEKDAY(DATE(DATE_FORMAT(`dt`,'%Y-%m-01'))-2)+LAST_DAY(`dt`)-DATE(DATE_FORMAT(`dt`,'%Y-%m-01')))/7) /2 )