1 2 Previous Next 21 Replies Latest reply on Oct 27, 2017 12:44 PM by Don Wise

    Firefighter Shift Schedule (9-day Repeating)

    Don Wise

      Has anyone attempted to apply a calculated measure for a firefighter shift schedule?  Or best way to do this in Tableau?

       

      Based on a particular start date and then by subsequent distinct date values the rotation is as follows in a 9-day cycle and repeats itself in a distinct pattern thereafter:

       

      E.g.,

       

      01/01/2016 = A-Shift

      01/02/2016 = B-Shift

      01/03/2016 = A-Shift

      01/04/2016 = B-Shift

      01/05/2016 = C-Shift

      01/06/2016 = B-Shift

      01/07/2016 = C-Shift

      01/08/2016 = A-Shift

      01/09/2016 = C-Shift

      01/10/2016 = Starts above 9-day cycle again

       

      Where each 24-hour shift is equal to a particular and distinct date.  When the measure (or dimension) is applied to a date value the column displays either "A-Shift" or "B-Shift" or "C-Shift" depending on that distinct date value.

       

      Thoughts?

        • 1. Re: Firefighter Shift Schedule (9-day Repeating)
          Bill Lyons

          Am I understanding that you are looking for a way to calculate the shift, given a particular date? If so, I believe the below calculated fields might get you started. What isn't clear to me is what to do at the end of the year, since 9 days does not divide evenly into a year.

           

          [Day of Year] = DATEPART('dayofyear',[Date])

           

          [Day of Shift Cycle] = [Day of Year] % 9

           

          [Shift] = CASE [Day of Shift Cycle]

                         WHEN 1 THEN 'A-Shift'

                         WHEN 2 THEN 'B-Shift'

                         WHEN 3 THEN 'A-Shift'

                         WHEN 4 THEN 'B-Shift'

                         WHEN 5 THEN 'C-Shift'

                         WHEN 6 THEN 'B-Shift'

                         WHEN 7 THEN 'C-Shift'

                         WHEN 8 THEN 'A-Shift'

                         WHEN 0 THEN 'C-Shift'     //Day 9 is zero in modulo division

                    END

           

          I hope that at least gets you started.

          1 of 1 people found this helpful
          • 2. Re: Firefighter Shift Schedule (9-day Repeating)
            Don Wise

            Hi Bill,

            First of all, thank you for responding. It’s what makes these community forums so great – the participation and help.  So, greatly appreciated.  I don’t think I could’ve come up with this on my own, so greatly appreciate the direction here.  I did use the below into a new calculated field but I’m getting the following syntax error with the bracket for Day of Shift Cycle highlighted.  Thoughts on how to correct the issue?

             

             

            • 3. Re: Firefighter Shift Schedule (9-day Repeating)
              Bill Lyons

              These are 3 different calculated fields. You are trying to put all 3 into one calculated field.

               

              Calculated field 1 is named "Day of Year"

              The formula inside the calculated field is: DATEPART('dayofyear',[Date])

              What this returns is the number of days since the beginning of the year. Jan 1 returns 1. Dec 31 returns 365 in non-leap years, and 366 in leap years.

               

              Calculated field 2 is named "Day of Shift Cycle"

              The formula inside the calculated field is: [Day of Year] % 9

              In this, the "%" symbol is the modulo operator. So, it returns the remainder of [Day of Year] divided by 9.

               

              Calculated field 3 is named "Shift"

              The formula inside the calculated field is:

              CASE [Day of Shift Cycle]

                             WHEN 1 THEN 'A-Shift'

                             WHEN 2 THEN 'B-Shift'

                             WHEN 3 THEN 'A-Shift'

                             WHEN 4 THEN 'B-Shift'

                             WHEN 5 THEN 'C-Shift'

                             WHEN 6 THEN 'B-Shift'

                             WHEN 7 THEN 'C-Shift'

                             WHEN 8 THEN 'A-Shift'

                             WHEN 0 THEN 'C-Shift'     //Day 9 is zero in modulo division

              END

               

              I hope that clarifies it for you.

              1 of 1 people found this helpful
              • 4. Re: Firefighter Shift Schedule (9-day Repeating)
                Don Wise

                Hi Bill,

                 

                We spoke too soon; your calculations worked as you’d recommended.

                 

                We were working with a limited set of “test” data and now with all the fields/columns populated, B-Shift appeared.  Thank you!

                • 5. Re: Firefighter Shift Schedule (9-day Repeating)
                  Bill Lyons

                  Great. Glad I could help.

                  • 6. Re: Firefighter Shift Schedule (9-day Repeating)
                    Suzann Leininger

                    I have the same situation.  But, the shift does not start at midnight.

                     

                    How do you consider this when you have to have a start time each day of 0800 and end the next day at 0759?

                     

                     

                    This shift is:  one on, one off, one on, two off, one one, one off, one on, four off.

                    • 7. Re: Firefighter Shift Schedule (9-day Repeating)
                      Suzann Leininger

                      I got this to work for the 'day'.  Now I need to get this to work for the start time of 0800 and end time of 0759 the next day.

                       

                      I am new to Tableau.

                       

                      Thanks!

                      • 8. Re: Firefighter Shift Schedule (9-day Repeating)
                        Don Wise

                        Hi Suzann,

                        That's great!  To answer your question, it depends on what you're trying to evaluate...for example, my use-case scenario was to evaluate by Shift, a count of the number of incidents or responses for each Shift for each Fire Department that we serve (14 of them). 

                         

                        In Tableau, we use our "Dispatched" time/date field as a COUNTD.  Between the "Shift" calculation in this thread and our "Dispatched" date/time stamp (because only those incidents are within the same time frame as the Shift), Tableau will filter automatically all Dispatches for the appropriate shift across that 24-hour time frame and count specifically for those parameters. So you might not need to do anything else if that's the case; we haven't had to set a shift period at this point.

                         

                        If you're trying to do something else, which requires a specific time period to filter to, then there'll be a need for a separate calculation as a filter, something like this (modified from KK Molugu original post Re: Separating shifts - Split Shifts ):

                         

                        // 24-hour Firefighter Shift

                        // For the first shift segment (hours 0800-0000), keep the Date as is

                        // For second shift segment crossing over midnight (hours 0000-0759), set the Date to the next day from 0-8

                         

                        // From Hours 0800-0000

                        IF  datepart('hour', [Dispatch]) >=8 and datepart('hour', [Dispatch]) <=23 then [Dispatch]

                           

                        // From Hours 0000 to 0759

                        ELSEIF  datepart('hour', [Dispatch]) >=0 and datepart('hour', [Dispatch]) <8 then

                            dateadd('day', -1, [Dispatch]) END

                         

                        I just vetted this calculation against our data and it appears to be working well (screenshot below), starting the shift at 0800 and ending the next day at 0759.  Hope this is what you needed?  Thx, Don

                         

                        Screen Shot 2017-10-21 at 10.57.37 AM.png

                        • 9. Re: Firefighter Shift Schedule (9-day Repeating)
                          Suzann Leininger

                          Thanks so much for the response. 

                           

                          I will try this when I get back into the office.

                           

                          We also serve multiple agencies (15) and they don't all have the same start time or schedule.

                           

                          I am not real clear about your comment:

                           

                          In Tableau, we use our "Dispatched" time/date field as a COUNTD.  Between the "Shift" calculation in this thread and our "Dispatched" date/time stamp (because only those incidents are within the same time frame as the Shift), Tableau will filter automatically all Dispatches for the appropriate shift across that 24-hour time frame and count specifically for those parameters. So you might not need to do anything else if that's the case; we haven't had to set a shift period at this point.

                           

                           

                          • 10. Re: Firefighter Shift Schedule (9-day Repeating)
                            Suzann Leininger

                            My browser started acting up so need to add to my reply:

                             

                            You are filtering by date and hour of response time? 

                             

                            Thanks so much, Suzann

                            • 11. Re: Firefighter Shift Schedule (9-day Repeating)
                              Don Wise

                              Hi,

                              Yes the calculation filters by date and hour of whatever date/time stamp field you decide to use. For us it will be their Dispatch timestamp.

                               

                              Ahh, yes to clarify your last, we initially used the Shift Calc in this thread and also tied a shift period filter as there wasn't anything in our initial data set that indicated what period an agency was working.  That was over a year ago. We've since done a table join of the Shift Schedule for each Fire Department to our incident data so that it's all a part of the initial data set, so we no longer have to use a "Shift Period".

                               

                              Because we can simply use the Shift field as a dimension, any incidents that fall into that shift are automatically filtered for that 24 hour period for that particular shift (0700-0659) because Tableau recognizes that a particular incident is tied to a particular shift.

                               

                              Because I haven't seen how your data schema is constructed I was assuming you might be doing it one way or another...

                              • 12. Re: Firefighter Shift Schedule (9-day Repeating)
                                Suzann Leininger

                                Is there any way I can see that table that you join to Tableau?  I tried that initially but could not get it to work.

                                • 13. Re: Firefighter Shift Schedule (9-day Repeating)
                                  Don Wise

                                  Sure...below are screenshots of a MS-Excel file.  There are two worksheets in the workbook in this example.  The first sheet is the original data as provided by each agency.  The way the data is shaped, will not work as a join in Tableau.  It has to be transformed. The second sheet is the reshaped/transformed data which is subsequently used in the Tableau Data Window as a join.

                                   

                                  A Tableau screenshot example is also below which shows how that join is performed (using the second sheet from the workbook) with the primary data set which is coming from MS-Access, in this example.  In real life we connect to SQL Server.  The below is simply an example of how that can be done. 

                                   

                                  The join is performed on two matching fields, a date value and the agency (3-letter) ID value.  I noticed in your table that you have a date/time field.  You can parse out your date field as part of the join function by using the following Tableau function: DATE([YourDateTimeField])  Between those two fields you should achieve a 1:1 match using an inner-join.

                                   

                                  Screen Shot 2017-10-22 at 11.00.13 AM.png

                                  Screen Shot 2017-10-22 at 11.00.16 AM.png

                                  Screen Shot 2017-10-22 at 10.37.12 AM.png

                                  • 14. Re: Firefighter Shift Schedule (9-day Repeating)
                                    Suzann Leininger

                                    Thank you so much!  I will take a closer look at this tomorrow when I am back in the office.

                                     

                                    I really appreciate your help on this.

                                     

                                    Suzann

                                    1 2 Previous Next