Today minus one business day

[Muhammad]

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 Date	Desired Output Date
2019-04-04	2019-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]

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.

[Muhammad]

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

[danilang · Accepted solution]

Hi @Muhammad 

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

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

Dan

[Muhammad]

It worked! Thank you so much...

[Istwineres]

to remove 2 business days? Can the workflow remain the same?

[Sameer_Kanade8810]

Hello all i have a formula which gives me last business day according to the week. Now when we have us off my last business day still consider the us off date. Is there a way where i can store this us offs in the workflow itself and can give me a result of last business day by not counting the us off date.