Number of business days in the month

user060355
user060355 Member
edited October 7 in Beast Mode

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

Tagged:

Best Answer

Answers

  • Manasi_Panov
    Manasi_Panov Contributor

    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:

    image.png

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

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • user060355
    user060355 Member

    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
    TimD Member

    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
    user060355 Member
    https://community-forums.domo.com/main/discussion/comment/98753#Comment_98753

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

  • TimD
    TimD Member

    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
    TimD Member

    Just found this reference in Domo:

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

  • TimD
    TimD Member

  • TimD
    TimD Member

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

    Domo Dimensions Connector

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓
    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.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • ggenovese
    ggenovese Contributor
    edited October 10

    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 )

Categories