By: Eric Parker
Eric lives in Seattle and has been teaching Tableau and Alteryx for 5 years. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.
Most programs like Excel, Tableau, Microsoft SQL Server and Alteryx have a built in Date Difference (DATEDIFF) function. This function is great at letting you set a unit of measurement (i.e. year, month, day) and calculate the difference between a start and an end point.
However, most of them don’t have a function that easily let’s you calculate the difference in business days. Sure you could try to use some really complicated formula like this example in the Tableau forums, but will it be right for you? You might have slightly different holidays than the person that created that formula. You could create a macro in Alteryx like is suggested here, but that looks like it could be complicated as well.
Let me suggest a simpler alternative. This approach takes a bit of work up front but works seamlessly after that.
Create your own calendar data table that has a running column called “Business Day”. Any time there is a holiday or weekend the business day value just repeats the previous number. If you (or your IT team) builds this by hand, you get to completely custom define which values repeat (meaning those dates are weekends or holidays).
One note of caution; if you are calculating the difference in days across multiple years, it will probably be easiest not to restart the Business Day value for each year. That means if your data started in 2017 your table would look like this instead:
You can get away with restarting the Business Day values every year but that’s going to make your later calculations more complicated.
Alright, let’s put this all into practice. Imagine we have a table full of orders and we want to know how many business days it took us to process and ship those orders.
For the initial setup in Tableau, we will join the Date Master table to the Orders table twice. Once when Date (Date Master) = Order Date (Orders) and once when Date (Date Master) = Ship Date (Orders).
Then, we can create a calculated field which finds the difference between the business day for the Ship Date from the business day for the Order Date.
Here the calculation is in action:
Let’s create a standard date difference calculation to compare:
Here’s the result:
Definitely some pretty different results. Do you have a different method you prefer for calculating difference in business days? Comment below or send us an email, we’d love to hear from you.
Want help with your own Tableau questions? Sign up for an office hour and we’ll help you!