Data Interval Using Business Days In MagicETL
I am calculating deadlines by using a dynamic formula in MagicETL, which adds 4 days to a date like so:
CASE WHEN `issue` = 'bug' THEN ( CASE WHEN `priority` = 1 THEN ADDDATE(`CreationDate`, INTERVAL 4 DAY) END )
This works great, however I'm wondering how I could change the interval to simply be four business days instead of four calendar days.
I see in the documentation that there is a page for calculating business days between two dates - https://domohelp.domo.com/hc/en-us/articles/360043428153-Date-Difference-for-Business-Days but it doesn't cover adding business days to a date.
I also see within MagicETL, there is a date operations which allows us to use a "Add to date" operation but business days is not a unit of measurement. Any ideas on how I could update the case statement to be 4 business days rather than calendar? Thanks a lot!
Answers
-
@adamxor - Do you have a dataset that contains a list of days and whether they are a "business day"?
If you are only wanting to exclude weekends, then you could write a statement that changes the number of days to add based on the day of the week of
CreationDate
.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
There's also a Domo Dimensions dataset that has a calendar and contains the more popular holidays as well as a weekend flag. You could use that dataset as a base and add/remove holidays as needed.
I recently set up a solution that would do a mini cartesian join (JOIN between start & End Date) to expand out the days between create/end dates and aggregate back down so you can then subtract the correct number of days after your datediff calculation
1 -
I've written up a very simplistic example of adding business days which doesn't take into account holidays or when the business may be closed. @Gordon_Pont is a more correct solution to account for these holidays and weekends but wanted to share how you can roughly do it with a formula: https://dojo.domo.com/main/discussion/52678/domo-ideas-conference-beast-modes-add-business-days#latest
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 194 Visualize
- 253 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive