Data Interval Using Business Days In MagicETL

adamxor
adamxor Member
in Magic ETL

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

  • ST_-Superman-_
    ST_-Superman-_ Coach

    @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.” -Superman
  • GordonP
    GordonP Domo Employee
    edited June 2022

    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
    GrantSmith Coach

    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!**

Categories