Alteryx Designer Desktop Discussions

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

Today minus one business day

Muhammad
7 - 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)
6 REPLIES 6
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.

Muhammad
7 - 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
danilang
19 - Altair
19 - Altair

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

Muhammad
7 - Meteor

It worked! Thank you so much...

Istwineres
8 - Asteroid

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

Sameer_Kanade8810
7 - Meteor

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.

Labels