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



    **Was this post helpful? Click the heart icon**

    **Did this solve your problem? Accept it as a solution!**
  • 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.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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.



    **Was this post helpful? Click the heart icon**

    **Did this solve your problem? Accept it as a solution!**
  • hqu
    hqu βšͺ️

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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon πŸ₯·

    @hqu I believe I have come up with a solution for you. It involves creating a MySQL dataflow and creating a function to calculate the next workday and then using the function in your call to your dataset. Here's what to do

    Create a MySQL dataflow and add in your dataset and the Domo Calendar dataset.

    Click Add a Transform and choose SQL and add the following which will allow you to run it multiple times when building out your dataflow

    DROP FUNCTION IF EXISTS NextWorkday;
    

    Add another SQL transform and add the following to create the actual function:

    CREATE FUNCTION NextWorkday (startdate DATE, days INTEGER)
    RETURNS DATE DETERMINISTIC
    BEGIN
    
    DECLARE nextdate DATE;
    
    SELECT `dt` INTO nextdate
    FROM `domo_calendar`
    WHERE `dt` >= DATE_ADD(startdate, INTERVAL days DAY) AND
    `isWeekday` = 1 AND `isHoliday` = 0
    ORDER BY `dt` ASC
    LIMIT 1;
    
    RETURN nextdate;
    END
    

    Now add a table transform where you can call this function as another column along with the rest of the columns that you need for your dataset like this:

    SELECT NextWorkday(`dt`,`days`) as NextWorkday, `dt`
    FROM `mytable`
    
    

    You'll need to change this to match your fields. Hope this helps.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.