Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Select Random Dates Excluding Weekends and Holidays

Zenn6420
7 - Meteor

Hello,

 

I am just starting out in Alteryx and in need of help from you guys. I need to select 25 random dates from May 1, 2022 to August 31, 2022 excluding weekends and holidays to inspect the reports sent on those days. How do I do this? 

 

Thank you very much.

5 REPLIES 5
DataNath
17 - Castor

@Zenn6420 something along these lines will allow you to do just that, and will continue generating a new 25 each time you run. The workflow:

 

1) Uses the Generate Rows tool to generate the date range you provided

2) Uses a Filter to remove all weekends

3) Assigns a random number to each row using the Rand() function

4) Sorts the rows based on this randomly-generated number

5) Picks the Top 25 rows

 

DataNath_0-1664274776533.png

 

To eliminate holidays, you'll just need to add them to your filter and that will get rid of those as well so you'll only be left with days of interest to pick out.

 

You can also use the Random % Sample tool - which is a macro that essentially does the same as above - and just select to pick out a random 25 records:

 

DataNath_1-1664274797161.png

Zenn6420
7 - Meteor

Hello @DataNath,

 

Thank you so much for creating a workflow! The Generate Rows fucntion was something I'm missing. I'm sorry but I still have another question. For example I integrate the holidays (June 20 and July 4, 2022) as one of the filters, how do I do it? Do I have to use a text input or integrate it in the Custom filter formula? 

 

Appreciate your time and help!

 

 

DataNath
17 - Castor

No problem @Zenn6420 - happy to help!

 

To exclude dates like this, you can just add them to your Filter expression like so:

 

DataNath_0-1664277609517.png

 

The '!=' operator just means not equals and so you're excluding those two dates here. Just make sure your dates are in YYYY-MM-DD format and in quotes like in my screenshot. Have attached the workflow with those 2 dates gone too.

Zenn6420
7 - Meteor

Thank you so much @DataNath!

 

DataNath
17 - Castor

No problem at all! Forgot to mention that - especially if you have quite a lot of specific dates to exclude - it's a lot easier to use 'not in' and create a list of these dates within brackets, separated by a comma, like so, rather than constantly adding 'AND != X' for every day:

 

DataNath_0-1664282284534.png

Labels