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.
Hey Łukasz Majewski, this solution is epic and looks exactly what i'm after. It would appear we are analysing the same workforce tool
Could talk through the steps if possible? I'm having a little bit of difficulty getting it to work.
I've attached my workbook.. It appears my "test" sheet is working ok to select through individual hourly buckets.
I'm having trouble with the "working employees" sheet to get the hour axis working correctly :/
I wanted to be able to view a bar chart of number of employees by date by hour ie a continuous date of 23/10 -> 24/10 -> 25/10 by a time bucket ie 1 hour. Would be good if can change this time bucket to say 15mins as well.
Any help much appreciated !
Time Hrs.twbx 74.3 KB
Hey Łukasz Majewski ! Any help GREATLY appreciated! I'm getting desperate and I can't use the other method joining with Excel as I'm on self-service and can't use the required join conditions to make it work.