Hi Peter

Welcome to the forum!

2 quick questions -

1. Am I right that in your example you would want the difference (in time) between lines 5 + 7? < because it's the same worker
2. Do you have v9?

Cheers

Mark

Third question -

Why does the worker's time end before it started?

Hi Peter

Assuming Yes to the above

First Start Date

{INCLUDE [Name] : MIN([Date])}

Then End Date

{INCLUDE [Name] : MAX([Date])}

This first step is to take the lowest date (the start) and the highest date (the end) and then use them for the calculation - we need v9 to use LOD (the {} brackets) this fixes the value by each name (otherwise we wouldn't get different dates for different people)

Then the datediff

DATEDIFF('minute',[Start],[End])

Attached is a basic working version in 9.1.1

Cheers

Mark

Hi Peter

Apologies I crossed over with your reply and additional information... I'll have another look and revise it

For example in this

{INCLUDE [Name] : MIN([Date])}

we need to take into account that it should be recalculated each day not as a one off...

I also need to figure out how to deal with breaks...

Leave it with me

Cheers

Mark

Below is a custom SQL solution with a correlating subquery as column. That said, table calculation solutions do also exist. If you find a search worthy of your time, you probably find it in  FAQ: Open & Close Dates.  That said, you might also hope someone knowing how will read this thread and share one or more table calculation approaches.

This is the query used in attached workbook:

SELECT [i].[Name], [i].[Date] AS [Date In]
, ( SELECT MIN([o].[Date])
    FROM [Pracownicy_przejscia$] o
    WHERE [o].[In/Out] = 'Out'
    AND [o].[Name]  = [i].[Name]
    AND [o].[Date] >= [i].[Date]
  ) as [Date Out]
FROM [Pracownicy_przejscia$] i
WHERE [i].[In/Out] = 'In'

As for table aliases, In and Out would be easier to read, but afraid IN is a reserved word. Therefore i and o were used.

It might be better to use [o].[Date] > [i].[Date] than [o].[Date] >= [i].[Date].

Some In's have no Out's as shown in screenshot below.

One way to solve this is to add a condition that the out has to be in the same day.

Another way is to set it to null if length is greater than for example 16 hours.

A third option is to have clean data 

But this you know better being closer to the source data.

Since Excel was the source, we need to use the Legacy Connector:

It would be nice if Tableau generally supported correlated subqueries and therefore posted  Correlated Subquery.

If you agree, you may influence Tableau to do something about it by up-voting it.

Performance tip: The attached is saved as an extract because subqueries in Excel (SQL Jet) don't perform well.

Attached Workbook Version:  9.0

.

I knew if I wait long enough... kettan will come by a custom SQL solution

I had given it a good go with table calcs but as you mention, it isn't directly supported so you have to hack something, my issue has been making it robust... 1 record for each (start/stop) - I have done above.

Same thing - making sure its robust

Some In's have no Out's as shown in screenshot below.

One way to solve this is to add a condition that the out has to be in the same day.

But this you know better being closer to source data..

Peter - custom SQL (or similar approach) maybe your only way to solve this. Effectively Kettan is joining the table back to itself in order to create the environment/ data structure to then look at times between date/times.

If your source is Excel, SQL then you're fine... what is your source?

Great work Kettan - really

Cheers

Mark

Hi Peter

I don't have a Mac but found this online help

http://onlinehelp.tableau.com/current/pro/online/mac/en-us/help.htm#customsql.html%3FTocPath%3DConnecting%2520to%2520Dat…

and an old thread Re: Hello, in version 8.2, where is the custom sql option in the excel workbook connection ?

Any problems - write back!

Cheers

Mark

I am afraid you have to uncorrect my answer   because seemingly* there is no legacy connector available for Mac as mentioned in  Re: Muliple tabs in Excel merge  and  Re: "The Sheet1 (Data.xls) data source is not supported on Tableau Desktop on the Mac. Before the data source can be refreshed, the data source must be upgraded."

Besides a table calculation, you could likely also make an Excel formula that looks up "the other date".

I think I have done so before, but need to refresh my Excel knowledge before sharing such a formula.

If it is okay to sort your data as the first step, then the formula can be relatively simple.

* I don't know if the legacy connector is available for Mac

You are welcome 

As for a workaround with formulas in the spreadsheet, the attached has it.

It is based on first sorting the Excel/LibreOffice sheet by [Name], [Date] Desc and [In/Out].

To avoid duplicates, filter it to [In/Out] = "In" (or "Out") only in Tableau Desktop.

Ps. I used  LibreOffice Calc 5.0.2  but confident that the formulas also work in Excel.