4 Replies Latest reply on May 14, 2018 2:56 PM by Andrew Klein

# Calculate datediff between inspection times for each site

Hello,

The challenge I have is how to calculate the difference between inspection times where the inspection has occurred at the same site.

The data I have is site number, inspection date, and inspection start time. The data I receive is not sorted in any order.

I can combine the date and time columns into one data column using the below code

DATETIME(STR([Insp Date]) + " "

+ STR(DATEPART('hour',[Insp Time]))+ ":"

+ STR(DATEPART('minute',[Insp Time]))+ ":"

+ STR(DATEPART('second',[Insp Time])))

The difficulty I’m having is how to calculate the difference in minutes between each sites inspections.

A sample data set is copied below:

 Site_NO INSP DATE INSP TIME A14899 1/04/2018 9:30:41 A14899 1/04/2018 11:40:33 A55799 1/04/2018 7:42:46 A14899 1/04/2018 8:17:59 A55799 1/04/2018 11:22:15 S01499 1/04/2018 9:51:47 S01499 1/04/2018 11:06:42 S01499 1/04/2018 15:32:07 S01499 2/04/2018 16:30:32 S01699 1/04/2018 7:28:01 S01699 1/04/2018 5:34:10 A55799 1/04/2018 16:51:49 A14899 1/04/2018 16:07:59 A14899 1/04/2018 8:16:33 A55799 1/04/2018 14:47:42

The desired output I need is in the final column below:

 Site_NO INSP DATE INSP TIME DESIRED OUTPUT:Time since previous inspection A14899 1/04/2018 8:16:33 A14899 1/04/2018 8:17:59 0:01:26 A14899 1/04/2018 9:30:41 1:12:42 A14899 1/04/2018 11:40:33 2:09:52 A14899 1/04/2018 16:07:59 4:27:26 A55799 1/04/2018 7:42:46 A55799 1/04/2018 11:22:15 3:39:29 A55799 1/04/2018 14:47:42 3:25:27 A55799 1/04/2018 16:51:49 2:04:07 S01499 1/04/2018 9:51:47 S01499 1/04/2018 11:06:42 1:14:55 S01499 1/04/2018 15:32:07 4:25:25 S01499 2/04/2018 16:30:32 24:58:25 S01699 1/04/2018 5:34:10 S01699 1/04/2018 7:28:01 1:53:51

Andrew

• ###### 1. Re: Calculate datediff between inspection times for each site

Are you looking for below?

I created a data item for this. Lookup function gives you value in values in the specified row.

I am also attaching packaged workbook in tableau 10.4

DATEDIFF('minute', LOOKUP(ATTR([date and time] ),-1) ,attr([date and time]))

1 of 1 people found this helpful
• ###### 2. Re: Calculate datediff between inspection times for each site

Hi Arvindgarg,

Thanks for your response.  I think this might solve my problem but is there any chance you could provide your packaged workbook in version 10.3 or earlier. Unfortunately I can't open 10.4 and would like to see how you have calculated the 'pre date time' etc?

Also does the data need to be sorted by site number for this formula to work?

Thanks

Andrew

• ###### 3. Re: Calculate datediff between inspection times for each site

Attaching in tableau 10.3.2

• ###### 4. Re: Calculate datediff between inspection times for each site

thanks this is great