7 Replies Latest reply on Oct 1, 2018 9:09 AM by Don Wise

    Night Shifts

    Lisa Geller

      Hello All,


      My data contains info on Emergency Department patients.  I am trying to create a dashboard that shows the number of patients that come to the ED per shift.  I have been able to create calculated fields that show the number of patient's that come in on day and evening shifts.  My problem is the overnight shift - which starts at 7pm and ends at 7am the next day.  I cannot figure out how to create a calculated field that will include the patients that come in after midnight because that is the next calendar day.


      For example, if a patient comes in on Monday Sept 15th at 2AM - I want that person to be included in the Sunday Sept 14th 7P-7A shift.  I have attached a screen shot of the formula I am using to try and include the patients that come in from midnight until 7am but I keep getting a syntax error.  I cannot attach my dataset because of patient privacy issues so I hope someone can help me out with just this info:






      Thanks so much,


        • 1. Re: Night Shifts
          Chris McClellan

          What's your error ?, it's working for me


          2018-09-29 13_03_52-Microsoft News.png


          There's no need to test for >= 0 because it will always be that.

          • 2. Re: Night Shifts
            Lisa Geller



            Your reply seems to correct the arrival date for those arriving after midnight but I can't seem to get a formula that will correctly identify the patient's that come in after 9pm.


            Here's what I wrote:



            //defines 9p-7a shift


            IF [Weekday]

            AND  DATEPART('hour', [Arrival]) >=21 and
            DATEPART('hour', [Arrival Date (for night shift)]) <7
            THEN "9p-7a" END



            All I seem to be getting are null values.


            Any ideas??

            • 3. Re: Night Shifts
              Don Wise

              Hi Lisa,

              We resolved this exact issue for dealing with Firefighters who work a 24-hour Shift and have to satisfy the issue of working into the next 'day' and capturing the number of responses they went on by using the following calc:


              // 24-hour Firefighter Shift

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

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


              // From Hours 0700-0000

              IF  datepart('hour', [Response Date]) >=7 and datepart('hour', [Response Date]) <=23 then [Response Date]


              // From Hours 0000 to 0700

              ELSEIF  datepart('hour', [Response Date]) >=0 and datepart('hour', [Response Date]) <7 then

                  dateadd('day', 1, [Response Date]) END


              So you're close to achieving it...hope this works for you.  Thx, Don

              1 of 1 people found this helpful
              • 4. Re: Night Shifts
                Lisa Geller

                I really do think it's really close.  I don't understand why I would have "1" in the dateadd part of the calc instead of "-1" since I want the people who come in between midnight and 7am to be counted with the previous days census. 


                I'm also having some trouble getting the results labeled as "9p-7a"


                Here's what I have :



                IF  [Weekday]


                AND  DATEPART('hour', [Arrival])  >=21
                AND  DATEPART('hour', [Arrival]) <=23 then "9p-7a"



                ELSEIF  DATEPART('hour', [Arrival]) >=0 and
                DATEPART('hour', [Arrival]) <7 then dateadd('day', -1,[Arrival]) THEN "9a-7p" END



                Tableau doesn't seem to like that last THEN.  I am getting a syntax error - I guess because I have 2 then statements in a row, which I know doesn't make sense but.how do I label the results as "9a-7p" so I can use them in a filter or parameter??




                • 5. Re: Night Shifts
                  Don Wise

                  Hi Lisa,


                  Yes, the DateAdd of "1" will look for any relevant data in the next day for the early morning time period involved.


                  If using '-1' then you're getting data from the early morning of the 13th (0000-0700) and not the 15th...if the 24-hour period involves the 14th starting at 0700 hours, per your example.


                  Please see newly attached sample workbook which emulates what you're trying to do?  Below is the screenshot of the example and the calculation used.  I did two things:


                  (1) Created a Shift Period using 'arrival time' from some sample data I had to set the entire 24-hour period from 0700-0659.  That part might not have been necessary but in order to truly capture the data I'd rather use it to get the data from the 0000-0700 period properly.


                  (2) Using that calculation, then created a Weekday Shift calc which can then be used as a filter or to color etc. by individual and segmented shift periods for all three.


                  Notice that the shift from 9p-7a is segmented correctly into the next date. Meaning, your counts and display/viz will also be more accurate.


                  Hope this helps you along.  If yes, please mark as correct so that others may find it useful in the future.  Thx, Don

                  • 6. Re: Night Shifts
                    Lisa Geller

                    Thanks so much Don!!  That is exactly what I have been trying to do!

                    • 7. Re: Night Shifts
                      Don Wise

                      Hi Lisa,


                      Glad to have been able to help! 


                      Best, Don