Alteryx Designer Discussions

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

Count working days

munchkin100
7 - Meteor

Hi! I have two sets of data: 

 

- "Records", which has the dates, when a request has been raised and when completed

- "BankHol and Weekend", which has the list of bank holidays and weekends

 

I would like to count, how many days did it take to complete the task. I would like to exclude the weekends and bank holidays.

 

I have tried to use a Join tool: L for "Records" and R for "BankHol and Weekend". I thought about using Left output, but then I realized that it would exclude any tasks raised at the weekend or bank holiday.  I cant use True/False for weekends either, as again I dont want to exclude tasks raised at the weekend, but I would like to exclude any bank holidays or weekend from the Open Days calculation.  So, if something was raised at 1/11 (Sun) and completed 17/11(Tue), then it should say that it took 12 days to complete the task.

 

I would appreciate any help.

8 REPLIES 8
AngelosPachis
15 - Aurora

Hi @munchkin100 ,

 

I've used  a generate rows tool to create all days between that of a task has started and that which finished. Then, with a Join tool, I join on the holidays-weekends dataset and whatever falls out of my R Join anchor are the dates that I need to count.

 

Screenshot 2020-11-26 131404.jpg

 

Hope that helps, let me know if you have any questions.

 

Cheers,

 

Angelos

atcodedog05
20 - Arcturus

Hi @munchkin100 

 

Here is a workflow for the task.

Output:

atcodedog05_0-1606396584165.png

Workflow:

atcodedog05_1-1606396597440.png

 

1. generate days between start and complete

2. remove holidays

3. summarize and count

Hope this helps 🙂 Feel to ask if you have any questions


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

 

Edit : We both posted so close few seconds difference😅

Buzz
8 - Asteroid

HI @munchkin100 ,

 

you an also use the python functionality. There is a dedicated function for it. The +1 in "df['Working Days'] = np.busday_count(A, B,holidays=holidays)+1" is added for having the Start Date/End Date also counted as a working day.

 

Buzz_0-1606401749344.png  Buzz_2-1606401764095.png Buzz_3-1606401771117.png

 

 

munchkin100
7 - Meteor

@Buzz , @atcodedog05 , @AngelosPachis  Thank you very much for your replies. All the solutions are brilliant. Shame I could highlight only one. I would mark all of them as a solution, if I could. Much appreciated! you guys saved my life!

atcodedog05
20 - Arcturus

Hi @munchkin100 

 

As a matter of fact you are allowed to mark multiple post as solution. Hence feel free🙂

 

Happy to help 🙂

 

Cheers and Happy Analyzing 😀

 

Feel free to reach out if you face any issues 🙂

AngelosPachis
15 - Aurora

Glad we could help @munchkin100 ,

 

To be fair, I think you can highlight/accept more than one solutions.

CarolAnn
5 - Atom

this is really great, but do you know how I can run it without having to change (remove) the "+1"?  I have a situation where a holiday lands on the end date and it does not calculate correctly unless I remove the "+1".

CarolAnn
5 - Atom

found this, I will try it:

Important to remember is that slicing with labels using the loc method expects inclusive ends, whereas slicing using labels using the iloc method expects exclusive ends.

Labels