1 2 3 Previous Next 31 Replies Latest reply on Oct 29, 2019 12:21 AM by Łukasz Majewski

how many employees are working by the hour of the day.

Hello,

I have the attached data set and I want to find out how many employees are working by the hour of the day.

I have the time stamps(punch in and punch outs in the attached Excel file).

How I can do that?

Thanks.

• 1. Re: how many employees are working by the hour of the day.

Hi Navid,

Thanks

Deepak

2 of 2 people found this helpful
• 2. Re: how many employees are working by the hour of the day.

Hi Deepak,

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.

Thank you.

• 3. Re: how many employees are working by the hour of the day.

Pl Find attached and Screenshot.

Thanks

Deepak

1 of 1 people found this helpful
• 4. Re: how many employees are working by the hour of the day.

Deepak,

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?

Thanks

Navid

• 5. Re: how many employees are working by the hour of the day.

In that case, use this.

1 of 2 people found this helpful
• 6. Re: how many employees are working by the hour of the day.

It still does not show the "number of employees by working hour of the day".

Here is the way I change the value to the "hour of the day".

we should have 24 columns from 0 to 23.

The one you created is still a continuous date(not a discrete)

• 7. Re: how many employees are working by the hour of the day.

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.

Filter

[Period] <= DATEDIFF('hour',[Startdtm],[Enddtm])

Step 4:  Create calculated reporting hour to use as time axis

Period Hour

Step 5:  Build view

See more in attached workbook.

Attached Workbook Version:  10.3

3 of 3 people found this helpful
• 8. Re: how many employees are working by the hour of the day.

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)

1 of 1 people found this helpful

• 10. Re: how many employees are working by the hour of the day.

Thank you Lukasz. This is a great idea. I am in rush right now, but I will compare both solutions later.

• 11. Re: how many employees are working by the hour of the day.

Deepak,

• 12. Re: how many employees are working by the hour of the day.

Hi,

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.

cheers,

Mic

• 13. Re: how many employees are working by the hour of the day.

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 !

Thanks,

Josh

• 14. Re: how many employees are working by the hour of the day.

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.

Ta!!

Josh.

1 2 3 Previous Next