3 Replies Latest reply on Feb 18, 2020 8:44 PM by Steve Wood

# Create a graph of hours per day based on a shift and by area showing hours with no data

I am trying to show the number of people working per day in an area.

I would like the hours per day on the X axis, showing all hours (0000,0100,0200 to 2300) even if there is no one working that hour

I would like to then show number of people working during each of those hours based on a shift on the 7 axis, colored by area.  Shifts in brackets are to be ignored as they are scheduled shifts that the person did not work.  The date is irrelevant.

Attached is a simple data set.

• ###### 1. Re: Create a graph of hours per day based on a shift and by area showing hours with no data

Hi James,

I reckon you're going to need to do some data prep / scaffolding for this.

First up the scaffolding: I joined each row in your data set onto a separate sheet in the Excel which has every hour from 0 to 23. The join condition here is where 1 = 1 so every row joins.

The "Hours" data source looks like this:

This means every row in your original data set now ends up having 24 copies, one for each hour 0 to 23.

You'll notice I've also added some calculations. These are doing the data prep - you may choose to do some of this in advance. I'll attach the workbook, but the quick version is: split your shift text up to get start time and end time as numbers, along with a flag indicating whether the shift was worked or not.

Once we have those we can filter out rows that were for shifts not worked. And we can create a calculation to count a worker when the hour (0 to 23) falls into the shift start and end times - this is slightly more complicated due to shifts that flow over to another day. The "IF" does the easy case ... is the hour between 6 and 17. The "ELSEIF" does the other case.

Num workers =

IF  [ShiftStartTime] <= [ShiftEndTime]

AND [ShiftStartTime] <= [Hour]

AND [ShiftEndTime] >= [Hour]

THEN 1

ELSEIF [ShiftStartTime] > [ShiftEndTime]

AND    ( [Hour] < [ShiftStartTime] OR [Hour] > [ShiftEndTime] )

THEN 1

ELSE 0

END

End result is then:

Is this roughly what you were expecting? Fingers crossed I've understood ... and got the calculations right! If not then let us know if it helps and/or if you need some more pointers.

Ta,
Steve

• ###### 2. Re: Create a graph of hours per day based on a shift and by area showing hours with no data

I have not confirmed the data(going to write a quick program to mash it and confirm) however this appears to be working. Instead of the not worked I just added a filter on Shift of 'if contains "(" exclude' .  I cannot figure out how to get the area's in. I see you are using marks however I am a Tableau newbie and do not understand it at this point