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

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

@Canioagain the formula shows up in the formula tile in Magic ETL. For whatever reason, the beast mode editor in Analyzer doesn't show as extensive list of functions, but 95% of the functions you see in the formula tile in Magic ETL will also work in the Beast Mode Analyzer.