Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

DateTimeAdd to Only Result in a Business Day

Brian32
8 - Asteroid

Thank you for looking into my inquiry.

 

I have an IF Statement that, based on varioius criteria, will subtract a specific number of days, respecitively, from a date column.  I don't want to only subtract Business Days.  All days should be accounted for in the subtraction, but what I need to have happen is that if the resulting date lands on a holiday or weekend I need it to go to the next business day.

 

Any ideas??

8 REPLIES 8
JohnJPS
15 - Aurora

If you have data that specifies date along with wether or not that date is a business day; you could dynamically query it with a where clause that grabs the max business day less than or equal to the day you pass in.  So you do your calculation and pass the result to the dynamic query.  If was already a business day, it will match as being equal to a business day in your data; if not, the "max less than" should return the correct day you're after.

 

Would that work for you?

 

Brian32
8 - Asteroid

It sounds like it would.  I am just not sure how to do what you are describing.

KaneG
Alteryx Alumni (Retired)

Hi @Brian32,

 

You can see what day a date is with '%a' (%A for full text) and so there is a couple of ways to do this. All in one formula would be something like this:

 

IF DateTimeFormat(DateTimeAdd([Date],-50,'days'),'%a')=='Sat'
THEN DateTimeAdd([Date],-50+2,'days')
ELSEIF DateTimeFormat(DateTimeAdd([Date],-50,'days'),'%a')=='Sun'
THEN DateTimeAdd([Date],-50+1,'days')
ELSE DateTimeAdd([Date],-50,'days')
ENDIF

 

You could make certain elements of this formula into their own variables naturally to make the formula more dyanmic.

 

Kane

Brian32
8 - Asteroid

Oh I see.  That makes sense.  Thank you!

JohnJPS
15 - Aurora

@KaneG, I like the nice, concise approach, however a word of caution that while it avoids weekends, it could still land on a holiday.  That's why I was wondering about @Brian32 having another dataset that told where the holidays were.  Just a thought.

 

KaneG
Alteryx Alumni (Retired)
To counter for holidays, you would need a lookup table with the holidays listed as they are different across states and countries. There is a few discussions on these forums about that including one from about last September that had about 20 comments and about 4-5 different macros/methods/solutions. Try searching for business days or networkdays... I'll have a look when I next air down at my computer.
JohnJPS
15 - Aurora

Here is a comment thread talking about finding the nearest value from a lookup table; it could perhaps be modified to solve our problem of finding ("looking up") the nearest business day less than or equal to a given day: https://community.alteryx.com/t5/Data-Preparation-Blending/Select-nearest-value-from-look-up-table/m....

 

Assuming we can limit the date table to a fairly small range (e.g. last year or even last 90 days or some such), then we wouldn't need to utilize the R approach suggested thereing, which performed better on larger data).

 

KaneG
Alteryx Alumni (Retired)

Just so that it's linked... here are some discussions on similar including several macros that people created for different incarnations of Business day with/without holidays and to get different calculations (date, or distance etc).

 

http://community.alteryx.com/t5/Data-Preparation-Blending/Datetimediff-using-only-weekdays-business-...

http://community.alteryx.com/t5/Data-Preparation-Blending/DateTimeDiff/m-p/3732/highlight/true#M582

 

Kane

Labels