Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
mathieuf
Alteryx
Alteryx

Background and Objectives

I have two dates and I want to know the number of working days between them.

 

mathieuf_0-1638870778634.png

 

 

I will count the number of days between these two dates by removing the weekends, holidays and customizable closing days. In the following example, there are 125 working days between the start date and end date:

 

mathieuf_1-1638870778550.png

 

 

And so that everything is reusable (yes we care about our carbon footprint!), I will make a macro that all my users can consume. 😎

 

Step 1: Suppression of weekends

To begin with, add an identifier to each line to make them easier to trace.

 

mathieuf_0-1638871187763.png

 

 

Then I create all the intermediate dates (as many rows as dates) using the Generate Rows tool. I create a new field that will contain the date of each day between the 2 milestones.

 

mathieuf_1-1638871278745.png

 

 

Result preview:

 

mathieuf_5-1638870778543.png

 

 

I then create a formula to know the days of the week:

 

mathieuf_2-1638871403035.png

 

 

Result preview:

 

mathieuf_7-1638870778548.png

 

 

All I have to do is filter the weekends:

 

mathieuf_3-1638871432002.png

 

 

Step 2: Deletion of customized closing days

For the deletion of closing days (days that are predetermined to be closed), we will propose a new input to users (list of days) that we will join to our previous flow.

 

The L input is our flow output with weekends removed and the R input is the list of closed days. By using the L output, we exclude the days of closure.

 

mathieuf_4-1638871675825.png

 

Step 3: Removal of public holidays

To remove the holidays, we will proceed differently. Not wishing to reinvent the wheel, I will use data from the Open Data made available by api.gouv.fr (it seems to me quite reliable 😉).

 

Thanks to this API, I have data on public holidays for the last 20 years and the next 5. It is also possible to filter data on regions or a specific year. This example use French public holidays. Open data for US, UK or anywhere else should be available at different locations.

 

Reliable and maintenance-free, what more could you ask for! I will use three tools in Designer: Text Input, Download and JSON Parse.

 

mathieuf_5-1638871977488.png

 

The Text Input tool contains the link to the data. The Download tool uses the link to get the data. I use the GET action:

 

mathieuf_6-1638872029661.png

 

Finally the JSON Parse tool structures the data to make the work easier. Here is what we get in output:

 

mathieuf_16-1638870778564.png

 

Not bad without data and almost without configuration!


I then keep only the dates in the JSON_Name column using a select and convert them to DateTime. The final touch is a join with our flow from step 2 and step 3:

 

mathieuf_7-1638872166848.png

 

By using the L output, we exclude those public holidays.

 

Step 4: We count and the trick is done

All we have to do now is count the number of days (number of rows) per identifier. (Even if there are several rows in the initial dataset, we are covered!) Aggregate and join with the data stream before the calculations and the work is done.

 

mathieuf_8-1638872354555.png

 

 

mathieuf_9-1638872379505.png

 

Step 5: The final touches

We choose a nice icon for the macro:

 

mathieuf_10-1638872435511.png

 

We document the tools:

 

mathieuf_11-1638872487977.png

 

We explicit the parameters:

 

mathieuf_23-1638870779203.png

 

And you can even customize your anchors:

 

mathieuf_24-1638870778568.png

 

 

Et voila!

 

 

Banner image by dennistrevisanph-5116225

Comments