Is there a equivalent Excel WORKDAY function in DOMO? Or is there any workaround way?

hqu
hqu βšͺ️

I have a start date, I want to know the next work day after a specified number of days. (exclusive of weekend and public holidays). In Excel, WORKDAY function can do this.

Would like to know how to achieve this in DOMO. Thanks for any input.

Answers

  • GrantSmith
    GrantSmith Indiana πŸ”΄

    Hi @hqu

    I've posted previously an article about adding business days to a date with a simplistic algorithm. You can read about it here: https://dojo.domo.com/discussion/52678/domo-ideas-conference-beast-modes-add-business-days#latest

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟀

    There is this beast mode function that is available, but it requires two dates, so may not suit your needs, but thought it is at least worth pointing out.

    DATE_WORKING_DIFF(expr1,expr2)
    

    Returns the number of days between the dates expr1 and expr2, excluding weekends (Saturdays and Sundays). The result is negative if expr1 is after expr2.

  • hqu
    hqu βšͺ️

    Thank you Grant, this is good, but it does not consider public holidays in.

  • hqu
    hqu βšͺ️

    Thank you Mark, days in between is not what I am looking for , but it is great to know. Thank you.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟀

    @hqu I would consider adding in the Domo Dimensions calendar dataset into your ETL. It has a list of all dates across a given range and has columns for IsWeekend and IsHoliday, which you could use to help with your counting. If you haven't used it for before, you can find it by going to connectors and search for Domo Dimensions and then add the calendar dataset.

  • GrantSmith
    GrantSmith Indiana πŸ”΄

    @hqu It doesn't. It's a simplistic model. As Mark said you'd need to pull in the Domo Dimensions - Calendar Dates dataset into an ETL. It's a bit more tricky with Magic as you can't do a conditional join with between or less than / greater than. You can do this with a MySQL dataflow but it will run slower than a Magic ETL. Magic will run slowly as well as you have to do a cartesian join to get all possible combinations and then do your filtering with a filter tile to get the information you want, then for each record group on the unique key, calculate the sum of weekday field and the sum of the is holiday field. Then subtract is holiday from weekday to get the number of business days in-between.

    Here's an example I did with a MySQL dataflow (dim_calendar_dates is the Domo Dimensions - Calendar Dates dataset):

    select p.`id`, count(`id`) as calendar_days, sum(`isWeekday`) - SUM(`isHoliday`) as business_days
    from `my_table` as p
    join `dim_calendar_dates` as d on d.`dt` >= p.`ship_date` and d.`dt` <= `delivered_date`
    group by p.`id`
    
    


    This is also assuming you're wanting American holidays, if you need holidays from a different country you'd need to get that dataset somewhere else instead of the Domo Dimensions connector.

  • hqu
    hqu βšͺ️

    Thank you both Grant and Mark, will look into your suggestions.