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

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
16 - Nebula

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
22 - Nova
22 - Nova

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
22 - Nova
22 - Nova

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
16 - Nebula

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