7 Replies Latest reply on Aug 28, 2018 5:37 AM by Ben Bird

# Calculate the difference of DateTime

Hello toghter,

I really hope somebody can help me with my issue.

My Data look like this…

DateTime                     Status         Var1     Var2

07.09.2017 15:31:10     1                A          100

07.09.2017 15:32:10     0                A          100

07.09.2017 15:32:16     1                A          200

07.09.2017 15:42:19     0                A          200

07.09.2017 16:42:21     1                B          100

07.09.2017 17:04:18     0                B          100

08.09.2017 16:05:55     1                C          400

08.09.2017 16:35:57     0                C          400

08.09.2017 16:36:03     1                C          400

08.09.2017 16:37:00     0                C          400

08.09.2017 17:49:06     1                D          600

08.09.2017 18:43:04     0                D          600

I want to calculate the difference between the DateTime. For example

(DateTime;Status;Var1;Var2)

(07.09.2017 15:32:10;0;A;100) - (07.09.2017 15:31:10;1;A;100)

(07.09.2017 15:42:19;0;A;200 )- (07.09.2017 15:32:16;1;A;200)

(08.09.2017 17:49:06;1;D;600) - (08.09.2017 18:43:04;0;D;600)

I have to admit that I use tableau since a few weeks so i hope for a good advice

• ###### 1. Re: Calculate the difference of DateTime

Marc,

I think the solution for you here is to reorganize your data to the following form:

 ID Datetime Status1 Datetime Status0 A100 07.09.2017 15:31:10 07.09.2017 15:32:10 A200 07.09.2017 15:32:16 07.09.2017 15:42:19 B100 07.09.2017 16:42:21  07.09.2017 17:04:18 C400 08.09.2017 16:05:55 08.09.2017 16:35:57 C400(2) 08.09.2017 16:36:03 08.09.2017 16:37:00 D600 08.09.2017 17:49:06 08.09.2017 18:43:04

You will need unique rows to associate the Date times to each other for your DateDiff Calculation

Once this is done, you will simply use the Calculation DATEDIFF('Minute(or other desired time)', <Datetime Status1>, <Datetime Status0>)

I'm unsure how to get what you want with the way your data is set up.

How are you pulling your data?  Do you have an ID column in your data source? there may be an easy way to change how the data is being pulled.

Hope this Helps.

• ###### 2. Re: Calculate the difference of DateTime

Not exactly sure about your final viz image, but hope this partially helps.

Thanks,

Shin

• ###### 3. Re: Calculate the difference of DateTime

Hey Ben,

today i get the data from a .csv file but in the futur i will connect straight to a sql.

i know i have to reorganize my data like in your example but how can i manage this?

today i have not a id column..

• ###### 4. Re: Calculate the difference of DateTime

Hey Shinichiro,

thx for your mind, this helps me a lot to understand how tableau and calculate fields works.

But i need the dataset like in bens recommadation. On this way its easier to build the viz.

• ###### 5. Re: Calculate the difference of DateTime

But you said you don't have row ID?

SHIN

• ###### 6. Re: Calculate the difference of DateTime

not in the .csv file.. iam unsure if i will have a id column in the sql.  :/

• ###### 7. Re: Calculate the difference of DateTime

Hey Marc,

I'm going to assume you will have an ID column in SQL,

If the Data in SQL is set up in the same way as you have it in the .csv file, you will want to use the following Query as a blueprint for your CustomSQL query:

Select

Q1.IDColumn

, Q1.Var1

, Q1.Var2

, Q1.DateTime as DateTimeStatus1

, Q2.DateTime as DateTimeStatus0

From (SELECT IDColumn, Var1, Var2, DateTime From <TableName> WHERE Status = 1) as Q1

LEFT JOIN (SELECT IDColumn, DateTime FROM <TableName> WHERE Status = 0)  as Q2 on Q1.IDColumn = Q2.IDColumn

This should format your Data correctly for easy use.

Hope this helps