community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Today minus one business day

Highlighted
Meteor

Hi,

 

I have a date field in my report, which is always the CURRENT date. I need to add another column that shows the previous working day date. Working days are Mon-Fri... Any help would be highly appreciated...

 

Example:

 

Current DateDesired Output Date
2019-04-042019-04-03
2019-04-01 (Monday)2019-03-29 (should show previous business day's date, i.e. Friday's date)
2019-04-08 (Monday)2019-04-05 (should show previous business day's date, i.e. Friday's date)
Inactive User
Not applicable

First question to ask yourself is what are statutory holidays for the business based on where you work, etc. Then, you need to parse out the day of week; https://help.alteryx.com/10.1/Reference/DateTimeFunctions.htm 

 

Then, you need have date add logic to go back x number of days depending on what day of the week it is. And using the holidays I mentioned you can remove those dates from the calc. Multi row formula can also work if you have a list of all applicable dates.

Meteor

Thanks Ryan... I'd really appreciate if you or someone could attach a sample workflow.. below is the list of stat holidays for remainder of 2019 and 2020...

 

Friday, April 19, 2019
Monday, May 20, 2019
Monday, July 01, 2019
Monday, August 05, 2019
Monday, September 02, 2019
Monday, October 14, 2019
Monday, November 11, 2019
Wednesday, December 25, 2019
Thursday, December 26, 2019
Wednesday, January 01, 2020
Monday, February 17, 2020
Friday, April 10, 2020
Monday, May 18, 2020
Wednesday, July 01, 2020
Monday, August 03, 2020
Monday, September 07, 2020
Monday, October 12, 2020
Wednesday, November 11, 2020
Friday, December 25, 2020
Monday, December 28, 2020
Nebula
Nebula

Hi @Muhammad 

 

Here's a simple workflow that gives what you're looking for.  

 

Result.png

 

The top branch is just your holiday list converted to Alteryx format. The bottom branch start by generating records for 5 days before your input date.  Remove the weekend days, join to the holidays and take the R output that doesn't match a holiday.  Find the max previous day grouped by input day.  I used an text input for the input days but in your case, you can change the generate rows configuration to this to just use DateTimeToday() and remove the lower text input

 

GenerateRows.png

 

Dan

Meteor

It worked! Thank you so much...

Labels