Data Interval Using Business Days In MagicETL

adamxor · 2022-03-28T14:50:06+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Accepted answers

All comments

ST_Superman

@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.

GordonP

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

GrantSmith

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