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

    James Sherwood

      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.

       

      Thanks in advance

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

          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
            James Sherwood

            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