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
-
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' thendt
end)2 -
@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:
2 -
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)
WHENNew Date 1
<'2024-06-26' ANDNew 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.
WHENNew 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
WHENNew 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.0
Answers
-
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' thendt
end)2 -
@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.
0 -
@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:
2 -
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)
WHENNew Date 1
<'2024-06-26' ANDNew 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.
WHENNew 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
WHENNew 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.0 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive