-
1. Re: DATEDIFF with condition - tracking working time
Mark Fraser Nov 3, 2015 2:50 AM (in response to Piotr Adamczyk)Hi Peter
Welcome to the forum!
2 quick questions -
- Am I right that in your example you would want the difference (in time) between lines 5 + 7? < because it's the same worker
- Do you have v9?
Cheers
Mark
-
2. Re: DATEDIFF with condition - tracking working time
Mark Fraser Nov 3, 2015 2:53 AM (in response to Mark Fraser) -
3. Re: DATEDIFF with condition - tracking working time
Piotr Adamczyk Nov 3, 2015 3:09 AM (in response to Mark Fraser)Hi Mark,
Acc:
#1. Not really but yes, mainly it's the same worker but that lines 5 and 7 aren't good example
I want to count difference (in time) between start and stop time in each day like here:
Line 23 and 22 have the same date 30.10 and that worker start at 06.46 and end at 15:36. I want to count that datediff for each worker and date and achieve pivot like that:
Next step will be a resume like:
A: how many times each worker made overtime in last week/month - I want to count salary for the overtime work.
#2. Yest, I have v9.1.
#3. Good question! That situation which you marked occurs when worker start for eg at 8:00 and make a brake at 9:30 and start again in 9:40 and finally stop at 16:00.
Please find the raw data attached.
-
work_time.xlsx 257.2 KB
-
-
4. Re: DATEDIFF with condition - tracking working time
Mark Fraser Nov 3, 2015 3:10 AM (in response to Piotr Adamczyk)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
-
192978.twbx 14.3 KB
-
-
5. Re: DATEDIFF with condition - tracking working time
Piotr Adamczyk Nov 3, 2015 3:31 AM (in response to Mark Fraser)Mark,
Thanks for the pretty fast and simple solution. How about brake in work. I think MIN/MAX count break as a working time.
How can I add that condition to the calculated field?
Regards!
-
6. Re: DATEDIFF with condition - tracking working time
Mark Fraser Nov 3, 2015 4:13 AM (in response to Piotr Adamczyk)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
-
7. Re: DATEDIFF with condition - tracking working time
Piotr Adamczyk Nov 3, 2015 4:18 AM (in response to Mark Fraser)Mark,
Please don't apologize me Thanks for your first solution. If you will find solution for breaks I will be really happy. Thanks in advance for your time!
Regards
-
8. Re: DATEDIFF with condition - tracking working time
kettan Nov 3, 2015 6:42 AM (in response to Piotr Adamczyk)1 of 1 people found this helpfulBelow 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
.
-
9. Re: DATEDIFF with condition - tracking working time
Mark Fraser Nov 3, 2015 6:29 AM (in response to kettan)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
-
10. Re: DATEDIFF with condition - tracking working time
Piotr Adamczyk Nov 3, 2015 6:37 AM (in response to Mark Fraser)Thanks Kettan and Mark for your time and solutions.
Yes, my source is Excel. Where can I find Legacy Connection in v9.1 for Mac? I can't find it.
@Kettan - I will vote up, for sure! One more time thx for the solution.
-
11. Re: DATEDIFF with condition - tracking working time
Mark Fraser Nov 3, 2015 6:47 AM (in response to Piotr Adamczyk)Hi Peter
I don't have a Mac but found this online help
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
-
12. Re: DATEDIFF with condition - tracking working time
kettan Nov 3, 2015 7:32 AM (in response to Piotr Adamczyk)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
-
13. Re: DATEDIFF with condition - tracking working time
Piotr Adamczyk Nov 3, 2015 6:59 AM (in response to kettan)Kettan,
I think, your answer is a solution, so I will leave it as a "Correct". I used your twbx and it is right. I am a mac user so I have to find another solution.
Maybe someone will find a solution for mac users. To this time I will prepare twbx with SQL on PC and proceed the rest on mac (yeah, I know it's a prosthesis )
Anyway, high-five for you and many thanks!.
-
14. Re: DATEDIFF with condition - tracking working time
kettan Nov 3, 2015 7:30 AM (in response to Piotr Adamczyk)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.