Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Background and Objectives
I have two dates and I want to know the number of working days between them.
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:
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.
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.
Result preview:
I then create a formula to know the days of the week:
Result preview:
All I have to do is filter the weekends:
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.
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.
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:
Finally the JSON Parse tool structures the data to make the work easier. Here is what we get in output:
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:
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.
Step 5: The final touches
We choose a nice icon for the macro:
We document the tools:
We explicit the parameters:
And you can even customize your anchors:
Et voila!
Banner image by dennistrevisanph-5116225
