Hello All,
Today in this blog we will cover a interesting scenario – Calculating the difference between two date-times and displaying the results in the format DD:HH:MM:SS ie the difference in time should be represented in the form of days , hours , minutes and second. So for example if we have two date-times as 07-Mar-2018 05:00:16 AM and 11-Mar-2018 03:56:11 AM then the difference between them should be shown as 3 Days 22 Hr 55 min 55 sec.
Doing this in Tableau requires us to build calculated fields for days, hours,minutes and seconds individually and them bringing them all together to display the result according to our format. For this tutorial we will use the excel file Sample_Datetime.
Download the above file and then import the file into Tableau. The excel file has three columns – OrderID, Order Date and Shipped Date.
Once the file is imported in Tableau , go to the sheet tab and change the properties of Order Date and Shipped Date to date&time by clicking on the Abc icon in from of these dimensions individually.
Once done right click on the Order Date , select default properties and then Date Format. A new pop window will appear and in that go to the custom format at the bottom and enter dd-MMM-YYYY hh:mm:ss AM/PM in the window that appears.
Do the same with Shipped date and now you will have the date time displayed perfectly in the view.
To check if our date time is displayed correctly Drag Order ID, Order Date and Shipped Date to Rows. Right click on the Order Date and Shipped dates and select Exact Date from the drop down and then convert them into discrete from continuous. Your view will appear like this:
Now we see that we have got the formatting of time to be perfect on the view. Lets move ahead to create the days, hours, minutes and seconds field individually.
The first field that we would be calculating would be difference between these two dates in seconds. We will use this value to extract the day, hour,mins and remainder seconds.
Calculation 1 : Time Diff In Sec
The above calculation gives us the total time difference in second. If we take the example that we considered earlier for the two dates 07-Mar-2018 05:00:16 AM and 11-Mar-2018 03:56:11 AM, the result would be 341755 seconds.
Calculation 2: Calculating the number of Days
Lets break this calculation in individual part and try to understand what is does.
A day has 24*60*60 seconds. If we do modulo division of the Time Diff In Sec with this value we would get the remainder seconds left. So in our case the total time in sec was 341755 , now 341755%(24*3600) gives us the value 82555. We subtract this value from the Time Diff In Sec to get seconds that are perfectly divisible by 24*3600.
Subtracting 82555 from 341755 gives us 259200 which is now perfectly divisible by 24*3600. When we divide this difference (259200) by 24*3600 we get the value 3 that gives us the number of days.
Calculation 3 : Calculating the Number Of Hours
The above calculation performs similar to the calculation of days. Here first we take modulo division of the remaining seconds (82555) with 3600. 82555%3600 gives us the result 3355 . We then subtract 3355 from 82555 so that the difference is perfectly divisible by 3600. 82555-3355 gives 79200 and when this is divided by 3600 gives us the 22 which in our case will be the no of hours.
Similarly we calculated the minutes and seconds as under :
Calculation 3: Calculation number of Minutes
Calculation 4: Calculating number of seconds
Once we have calculated the above field we make a final calculation as under to concatenate the above fields and show the output as desired.
The following calculation does that
Drag this calculated field in rows in front of the Shipped date and you will see the output as we desired .
Let move ahead a bit and try to calculate a Shipment Bucket calculated fields that would classify these orders into three categories as under :
Shipped Early : Shipments done within 7 days of placing the order
Shipped On Time : Shipments done in the second week after the order is placed
Shipped Late : Shipped after 14 days from the date of order .
To do that use the following calculation:
Using this calculation we can easily see the distribution of orders in these buckets as under.
We can now very easily tell that out of all the orders that were placed 277 orders were shipped late, 31 were shipped early and 22 were shipped on time.
The above concept of displaying the time difference finds multiple applications. I hope you guys enjoyed this post. So let me know if you are not able to follow any steps in the article.
