Easy Way to Measure Time in Business Days?

Is there a fairly simple way to do this or will I be looking at a fairly complex workaround? I am assuming that this may have to happen on the query side rather than in Domo but just wanted to make sure I am not missing anything.

 

Thanks,

Lucas 

Best Answers

  • zcameron
    zcameron Admin
    Answer ✓

    Lucas,

     

    There is an action in Magic ETL that can help you determine the difference between two dates in working days (weekdays). Use the Date Operations tile, selet "Difference between dates" as the operation, and then "Working days" as the unit of measurement.

     

    If you need a more advanced way of handling this that would include holidays or a custom schedule, it is a fairly complex setup. Most people use a calendar dataset to help with that. They can then run a subselect in a SQL query to count the days between the start and end that have appropriate features (is a workday, isn't a holiday, etc.). It isn't speedy, but it can work.

  • AS
    AS Coach
    Answer ✓

    One thing we've done to help in this regard is to add business day columns to affected datasets. We have a separate calendar dataset that includes, for each day, what business day of the month, quarter, and week each day is.  We combine that data into our other, transactional data.  Then it's a simple math operation to find out how many business days, for example, are between Mar 17th and Nov 4th.

    Going the opposite way, like finding the 20th business day ago from today, that's harder to do.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • zcameron
    zcameron Admin
    Answer ✓

    Lucas,

     

    There is an action in Magic ETL that can help you determine the difference between two dates in working days (weekdays). Use the Date Operations tile, selet "Difference between dates" as the operation, and then "Working days" as the unit of measurement.

     

    If you need a more advanced way of handling this that would include holidays or a custom schedule, it is a fairly complex setup. Most people use a calendar dataset to help with that. They can then run a subselect in a SQL query to count the days between the start and end that have appropriate features (is a workday, isn't a holiday, etc.). It isn't speedy, but it can work.

  • AS
    AS Coach
    Answer ✓

    One thing we've done to help in this regard is to add business day columns to affected datasets. We have a separate calendar dataset that includes, for each day, what business day of the month, quarter, and week each day is.  We combine that data into our other, transactional data.  Then it's a simple math operation to find out how many business days, for example, are between Mar 17th and Nov 4th.

    Going the opposite way, like finding the 20th business day ago from today, that's harder to do.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"