Counting Business Days Only.

[call_me_nasa]

Hello colleagues,

I am extremely new to Alteryx and what I am trying to do might be super simple...but I just don't have a clue in how to proceed.

Problem statement:

Currently, I am creating a field called "Time Group" in my output file and that field will have the following logic:

Counting today, bring me the next 10 business days and flag "Time Group" as "Within 10 Days".

From the day after the above statement, bring me the next 10 business days and flag "Time Group" as "Within 20 Days".

Example 1.

If date equals 1/12/2018, then the following dates need to be flagged as "Within 10 Days": 1/12/2018, from 1/15/2018 to 1/19/2018, and from 1/22/2018 to 1/25/2018. For a total of 10 business days.

Example 2.

If date equals 1/12/2018, then the following dates need to be flagged as "Within 20 Days": 1/25/2018, 1/26/2018, from 1/29/2018 to 2/2/2018, and from 2/5/2018 to 2/8/2018. For a total of 10 business days.

In terms of the holidays, I can try to deal with that later. The business need at the moments is creating these two buckets so we can get to the analysis part as soon as possible.

Thanks in advance!

[danrh · Accepted solution]

Give this a try:

I'm using Generate Rows to create the next 30 days worth of dates (just to be careful), then counting up the weekdays.  At this point I add a simple formula and join it to your data.

Hope it helps!

[CharlieS]

I have attached an example that should work for what you need. Here's a few key insights used to make it happen:

- I brute forced the days ahead by generating twice as many rows as the requested business days. The better way to do this would be to build an iterative macro that adds a day each iteration.

- The Alteryx DateTime formats of %a and %A provide the day of the week.https://help.alteryx.com/9.5/Reference/DateTimeFunctions.htm

[Philip]

Here's a solution I posted about subtracting holidays from business days. It also shows how I filter out weekends to leave only business days.

https://community.alteryx.com/t5/Data-Preparation-Blending/Subtracting-Holidays-from-Business-Days/t...

[Philip]

I'm not sure how you're going to do the analysis, but for the number of business days I'd use a Multi-Row Formula Tool inside an Analytical App with a date/time input and a number of days input with this run after removing weekends and holidays:

New Field: Count from first day

IF [Date Field] == [Input Date] THEN 0

ELSEIF [Row-1:Count from first day] >= 0 AND [Row-1:Count from first day] <= [Input Number of days] THEN [Row-1:Count from first day]

ELSE -9999

ENDIF

After, you can put a filter as [Count from first day] > 0 and you'll only have those fields.

As an alternative you could create this as a batch macro and input as many dates and number of days as you want for the input, plus the full business days data as an input within the macro.

[call_me_nasa]

Danrh,

Your solution worked!

I would like to thank you and all those who took from their time to submit their solutions. You guys rock!

[mchamps]

This macro published to the public gallery works very well!

https://gallery.alteryx.com/#!app/Working-Day-Time-Difference/5a0ad875f499c708d037257c

Detailed documentation can be found in the gallery link.