2 Replies Latest reply on Jul 26, 2018 3:35 PM by Don Wise

    How to calculate 3 Shifts a day

    phalondon pha

      hi every one,

       

      i have a time table as belows:

      i want to calculate 3 Shifts a day: shift 1: from 06:00 to 14:20; shift 2: from 14:20 to 22:30; shift 3: from 22:30 to 06:00 of the next day. the result i want to have is:

      can you please help me with the calculation?.  i have tested some calc but it gave me not correct results.

      Thankyou

        • 1. Re: How to calculate 3 Shifts a day
          Soham Wadekar

          Hi Phalondon,

           

          You can try

          IF DATEPART('hour',[Time]) >= 6 AND DATEPART('hour',[Time]) < 14.20   THEN '1st Shift'

          ELSEIF DATEPART('hour',[Time]) >= 14.20 and DATEPART('hour',[Time]) < 22:30 THEN '2nd Shift'

          ELSE 'Third Shift' END

           

          Just replace the ' ... ' with what ever you want to display. ie. IF DATEPART('hour',[Time]) >= 6 AND DATEPART('hour',[Time]) < 14.20   THEN 'from 6  to 14:20' and so on.

           

          Also, check your time column- some data formatting/ transformation might be required for tableau to recognize this as a time. But once that is done- the calculation should ideally work.

           

          Thanks,

          Soham

          • 2. Re: How to calculate 3 Shifts a day
            Don Wise

            Hello,

            You can try the attached Tableau workbook or look at the below screenshot.  Your time/date values need better formatting and to ensure that there were enough values I added a few more dates/times to ensure they grouped appropriately.  Your Excel worksheet is reattached as well with the new date/time values.  If this answers your question, please mark this response as correct.  Thx, Don

            Screen Shot 2018-07-26 at 3.30.39 PM.png