Insights with Viz and Analytics

Date Difference Calculation – Error We do always?

Let’s consider this business scenario:

I want to flag the Difference between two dates in days if difference between 2 dates more than X days, then I need to flag that row as “More than X Day”
else “Less than X Day”

Tableau Calculations Going to use in this Scenario:
DateDiff, IF then  else End

Calculation we normally write :
Day Diff = if Datediff(‘day’,start_date,end_date) >.= X days then “T” else “F” end

Consider we want to flag the dates if the difference days is more than 1

Start Date End Date Day Diff Our Calculation work like this?
15-02-2017 14:45:22 22-02-2017 10:45:26 7 F
18-09-2017 08:24:36 20-09-2017 20:25:36 2 F
12-10-2017 09:57:21 13-10-2017 12:07:24 1 T
10-11-2017 10:05:21 11-11-2017 09:07:25 1 T

But this calculation is wrong because our calculation is showing 1 day for the last row of dates but if you check the minute’s Difference between last row dates then it will be 23 hours only which means it’s technically not yet completed one day but our “DayDiff” Calculation showing 1 day which is wrong for many scenarios

Start Date End Date Day Diff Minute Diff
10-11-2017 10:05:21 11-11-2017 09:07:25 1 23

So to handle this I have written the calculation to handle the difference between days up to seconds Granularity.

if you see above image, the first row of dates showing day diff as “7” days but its technically not yet completed 7 days. Becuase for 7 days number of hours should be 168 Hours but when you see the hours difference it’s only 164, so it falls on below 7-day flag

Date Diff Calculation of Seconds Granular:

Validation = if DATEDIFF(‘second’,[Start Date],[End Date]) >= [More than X Day]* 24*60*60 then “More Than ” +STR([More than X Day]) + ” Day” ELSE
“Less Than ” + STR([More than X Day]) + ” Day”

More Than X Day – is the Parameter ( You can add any number of days you want )

Used 7 Number in the place of More than X Day Parameter, so the first-row show the Description as Less than 7 day which is right as per the scenario

Find out the workbook below:

« »

© 2019 TabVizAnalytics. Theme by Anders Norén.