Assistance to filter and identify last business day of month

[pattersonmichaelk]

Hi -

New to Alteryx and I'm stuck on filtering a column for the last business day of the month only. Format of the column with the date is below. Would be looking to filter out both July 31st and August 1st. Apologies if this wasn't posted properly or not in the proper location. Still learning.

End Date

30/07/2021

31/07/2021

01/08/2021

[Luke_C · Accepted solution]

Hi @pattersonmichaelk 

Here's how I would do this, although I'm curious if there's a better way.

1. Generate a record for each day
2. Add fields for the month & day of week
3. Filter out weekends
4. Summarize to get the max date per month (max non weekend day)
5. Join back to your list to get the ones that are the last business day.

You may need to add additional logic to account for your local holidays.

[pattersonmichaelk]

I should have added there are only 3 calendar days in the source file. Prior day, current day and future day.

[Luke_C]

Hi @pattersonmichaelk 

I'm not sure how that changes anything. If I'm understanding correctly, the source file has a column with dates, and there will only be 3 values. Of those 3 values, you wish to determine if any of them are the last business day of the month?

You'll see in my example it achieved the filtering you described and left us with the July 30th record.

[Tyro_abc]

Hi

Check this.

1. If the last day of the Month is Saturday - Result Shows Friday is the Last Business day

2. If the last day of the Month is Sunday - Result Shows Friday is the Last Business day

3. Others are Same..

 Regards

Tyro