Monthly Net Working Days

I am trying to calculate Net working days where working days is from Sun to Sat and the date in the dataset may or may not have date till EOM.

e.g.:

June'24 has 30 days with 5 Sundays, the beast mode should be able to calculate (30 - 5 = 25) when June'24 is selected from the filter and subsequently calculate Net Working days for other months as well.

Best Answers

  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    You might have to modify your dataset first, as you'll need to have all of the dates in your data if you want to calculate before the end of the month. I would probably start in Magic ETL with the Domo calendar dataset, via the Domo Dimensions connector, filtering it down to the appropriate date frame, and then left joining your dataset to it on your date field. This would keep all of the dates in the calendar intact. Then you could write a beast mode to count the Net Working Days, something like:

    count(distinct case when dayName != 'Sunday' then dt end)

  • DashboardDude
    Answer ✓

    @robinmathew131 Domo also has a calendar in Domo Dimensions with a isHoliday flag and a isWeekend Flag. So you can use that too to find working days to get to Net Working Days

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • ColinHaze
    ColinHaze Member
    Answer ✓

    I did something very similar, and I built it all entirely off of the Calendar Dataset. Ill walk you through how I did it at a high level and if you need clarification let me know. I'm also sure there are better ways but it took me a minute to get this working.

    I first used a case statement to assign the dates that I want for our fiscal months to the correct months. Our Months ends 3 days before the month is over.
    (Example)
    WHEN New Date 1 <'2024-06-26' AND New Date 1 > '2024-05-28' THEN '2024-06-01'

    I did this for the whole year. From there I entered in our holidays in a different formula tile and assigned them a number . For example if a holiday falls on a Monday, I would assign it 3(taking it to Friday) since we dont work on weekends.

    WHEN New Date 1= '2023-05-29' THEN 3

    After That I used date_sub to manipulate the date field. All this does is assign the date to the most recent work day
    WHEN New Date 1= '2024-05-27' THEN DATE_SUB(New Date 1, INTERVAL (Holiday) Day)

    From there I used a rank and window function the get the count of days. I used a group by to get the max Workdays in each month and joined it back.

    Here is what my outputted Dataset looks like. It has the actual workdays, Max weekends in the month, along with max workdays.
    Hope this help.


Answers

  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    You might have to modify your dataset first, as you'll need to have all of the dates in your data if you want to calculate before the end of the month. I would probably start in Magic ETL with the Domo calendar dataset, via the Domo Dimensions connector, filtering it down to the appropriate date frame, and then left joining your dataset to it on your date field. This would keep all of the dates in the calendar intact. Then you could write a beast mode to count the Net Working Days, something like:

    count(distinct case when dayName != 'Sunday' then dt end)

  • @Sean_Tully - Thank you for your support. I added a fiscal calendar with my dataset which has date wise Is Working day Yes or No and grouped it to get Monthly Net working days.

  • DashboardDude
    Answer ✓

    @robinmathew131 Domo also has a calendar in Domo Dimensions with a isHoliday flag and a isWeekend Flag. So you can use that too to find working days to get to Net Working Days

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • ColinHaze
    ColinHaze Member
    Answer ✓

    I did something very similar, and I built it all entirely off of the Calendar Dataset. Ill walk you through how I did it at a high level and if you need clarification let me know. I'm also sure there are better ways but it took me a minute to get this working.

    I first used a case statement to assign the dates that I want for our fiscal months to the correct months. Our Months ends 3 days before the month is over.
    (Example)
    WHEN New Date 1 <'2024-06-26' AND New Date 1 > '2024-05-28' THEN '2024-06-01'

    I did this for the whole year. From there I entered in our holidays in a different formula tile and assigned them a number . For example if a holiday falls on a Monday, I would assign it 3(taking it to Friday) since we dont work on weekends.

    WHEN New Date 1= '2023-05-29' THEN 3

    After That I used date_sub to manipulate the date field. All this does is assign the date to the most recent work day
    WHEN New Date 1= '2024-05-27' THEN DATE_SUB(New Date 1, INTERVAL (Holiday) Day)

    From there I used a rank and window function the get the count of days. I used a group by to get the max Workdays in each month and joined it back.

    Here is what my outputted Dataset looks like. It has the actual workdays, Max weekends in the month, along with max workdays.
    Hope this help.


  • Thank you all of you for your prompt help. I was able to get the desired result in my output dataset with the help you all provided.