Thank you for your response. I checked the thread, but I did not get how the calculation is done.
This is the end product I have in mind:
Could you please explain more in detail? I have attached the example workbook.
time-stamp.twbx 18.6 KB
Thanks for your reply. I follow your calculation, but some calculations do not make sense.
For example, if our start time is 6:20AM and end time is 11:40AM, we should add 1 to each discrete hours of 6,7,8,9,10 and 11.
First, Hour of value should be discrete, not continuous. If we change it to discrete, most of the buckets will be negative(which is impossible, because the least amount should be zero)
Right now, some hours are overwritten in graph.(1AM to 7AM)
How do you think we can edit this?
3 of 3 people found this helpful
Here is the method Deepak Rai referenced in Re: how many employees are working by the hour of the day explained step by step with your sample data. The only difference is that year is replaced with hour as argument in the date calculations. A reference to this is added to Split Periods Into Rows Dynamically with the hope that it will make it a little bit less difficult to understand and use this method.
Step 1: Add table "Period" in Excel
This table (together with a filter in Tableau) is used to split your periods into hourly rows.
WARNING: It is a must that it has at least as many rows as the row with most time units (hours).
Step 2: Connect and Join with 1 = 1
This is part one of two for splitting your periods into hourly reporting periods.
Step 3: Add a filter as the real "join"
This is part two of two for splitting your periods into hourly reporting periods.
[Period] <= DATEDIFF('hour',[Startdtm],[Enddtm])
Step 4: Create calculated reporting hour to use as time axis
Step 5: Build view
See more in attached workbook.
Attached Workbook Version: 10.3
If you're happy with stacked bars for hours where your working shift crosses midnight then this may be a solution too.
No cross join required (i.e. yuor data set wil not explode)
time-stamp 10.3.twbx 52.5 KB
Thank you Lukasz. This is a great idea. I am in rush right now, but I will compare both solutions later.
Thank you for your help.
I have a similar dataset that I would like to visualise as a dual axis chart, with the check-in time on the left and check-out time on the right; and each employee shown as a line from left to right. So employees which check-in and check-out immediately will be shown as a horizontal line. Would you be able to advise how that might be done? Many thanks in advance.