I wanted to thank you - you reminded me why I enjoy being part of this community (you also Kettan!)
Sorry again we couldn't get you a 'proper' working solution... yet!
All the best!
As I understand, second solution with excel calculations replace SQL formula and we can achieve the same result?
Correct me if I am wrong, please. Toady I have no access to the Tableau, so I will test that solution tomorrow.
I wanted to thank You and Kettan much more . I am really positively surprised by the quality and speed of your answers and of course I appreciate Your openness and patience. Thanks again!
It is correctly understood that the second solution doesn't use SQL.
It should have a filter such as [In/Out] = 'In' though for avoiding duplicated information.
I came across this question by coincidence... and I think you can calculate this without any custom SQL or LoD but solely with table calculations.
it takes into account 'breaks' - I called individual entrance a 'segment' and they may occur multiple time during reported period.
Depending on time period you use to partition (daily, monthly, etc) you may have different results since a segment may be broken, e.g. if a worker entered the building before midnight but left after midnight and you want to report daily. This partitioning could be removed from the 'segment time' calculation but I could not get rid of domain completion then...
Anyway, here are the calcs:
I also duplicated [Date] as [Date (copy)] to avoid domain completion. Both calculations are set to compute along [Date]:
And the results is in seconds.
Tracking Working Time 9.2.twbx 50.8 KB
Actually it may be addressed as follows - avoiding domain completion - and then the segments are not broken by new periods and it always takes time difference between OUT date and previous IN date regardless of when that previous IN occurred:
but you'll get strange results sometimes as your data seems incomplete:
Tracking Working Time 9.2.twbx 54.7 KB
Yes, my source is Excel. Where can I find Legacy Connection in v9.1 for Mac? I can't find it.
Even if this thread is relatively old, I thought it might be valuable for you and other readers to know that there probably (this isn't tested) is a way to imitate "Custom SQL" in Mac by "tricking" Tableau to CROSS JOIN with Tableau's join dialog.