Is there a equivalent Excel WORKDAY function in DOMO? Or is there any workaround way?
Answers
-
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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Thank you Grant, this is good, but it does not consider public holidays in.
0 -
Thank you Mark, days in between is not what I am looking for , but it is great to know. Thank you.
0 -
@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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
@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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you both Grant and Mark, will look into your suggestions.
0 -
@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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Did you have to request this? or is it now removed? I don't see this Date_Working_Diff anymore
0 -
@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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
5 years of using domo and I never knew there's a difference between beast mode and Magic.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 99 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 727 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive