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]
ELSEIF [ShiftStartTime] > [ShiftEndTime]
AND ( [Hour] < [ShiftStartTime] OR [Hour] > [ShiftEndTime] )
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.
ShiftWorkers.twbx 10.4 KB
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
Thanks for your help
No problem - great that it seems to be working!