Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.
A common question that often comes up in trainings is:
“How do I run a ‘difference between two dates’ calculation only considering business days?”
In order to focus on business days, we need to adjust our calculated field to define what a “Business Day” is. First, we need to be aware that Tableau gives each weekday a numerical value shown in the table below:
The first calculation checks to see how many weeks have passed between the start date and the end date. That result is multiplied by 5 (represented by 5 business days in a week) to assign the total number of business days that have passed. For example if a product takes 3 ½ weeks to manufacture, this calculation would return 15. We need keep in mind this number sequence when creating our calculated field so that we can remove Saturday and Sunday. The calculation can be done in one step, but it is much easier to talk about each part individually.
The next calculation returns the minimum value between numerical value (remember the table?) of the weekday of the end date and 6. Why 6? Think of this step as checking to see if the weekday is somewhere between Sunday (1) and Friday (6). This takes care of removing Saturday from our calculation. For example if a product ships on Tuesday, the value returned is the minimum of 2 and 6, which would be 2.
Lastly, we need to make the same calculation as above but this time for our start date.
Now to combine them all together! (You can reference the smaller calculations or write it all in one.)
The first calculation that determines the number of weeks will be our base. We can then add the second calculation, which returns the number of the weekday and overshoots the actual value. We need to correct for this by subtracting off the third calculation (return of the number value for the end date).
OR
A simple example would be:
Start Date End Date
Oct. 2, 2015 Oct. 26, 2015
DATEDIFF( ‘week’, Oct. 2, 2015, Oct. 26, 2015)* 5 = 15
+
MIN(DATEPART(‘weekday’, Oct. 26, 2015) ,6) = 5
–
MIN(DATEPART(‘weekday’, Oct. 2, 2015) ,6) = 2
Total Business Days = 18
In the example below, we can compare our business day calculation with the date difference calculation (DATEDIFF( ‘day’, [Order Date], [Ship Date])) and see that the weekends are not included.
Note: This does not include the first day in the calculation. Wrapping the entire calculation in () and adding 1 would correct this.
When you use your new field in the view, remember to pay attention to the aggregation Tableau uses and make sure it is set to how you want to view your data. Using Tableau’s default sum function will combine rows with the same date values and may give a much larger number than expected, whereas forcing the calculations to have the attribute function will ensure that they are computed at a row / transactional level – as used in the calculation above.
