5 Replies Latest reply on Nov 29, 2016 5:25 AM by Shinichiro Murakami

    Help with calculating shift times

    Billy Russell

      So the question is pretty basic but with a wrinkle thrown in. I want to create a field based on a date field of what shift it took place on. Shifts are:

      1st: 7am to 3pm

      2nd: 3pm to 11pm

      3rd:  11pm to 7am

       

      I was easily able to whip up a calculation for this:

      IF DATEPART('hour',[Opened]) >= 7 AND DATEPART('hour',[Opened]) < 15 THEN 'First Shift'

      ELSEIF DATEPART('hour',[Opened]) >= 15 and datepart('hour',[Opened]) < 23 THEN 'Second Shift'

      ELSE 'Third Shift' END

       

       

      Then my boss threw me the first curveball. He wanted to separate by weekday and weekend. Seemed easy enough so I threw this together:

       

      First I made a day of week field:

      DATENAME('weekday',[Opened])

       

      Then I created a Part of Week field:

      IF [DayofWeek]="Saturday" THEN "Weekend"

      ELSEIF [DayofWeek]="Sunday" then "Weekend"

      ELSE "Weekday"

      END

       

      All of that was relatively easy. Then my boss decided he wanted to see the data with a bit more granularity. So he wants separated charts showing weekend and weekday work separated into shifts. That's when I realized my original shift calculation wasn't entirely correct. Because I work Mon-Fri I forget that the weekend people have a different shift schedule. So the full shifts are as follows:

      Monday through Friday:

      1st: 7am to 3pm

      2nd: 3pm to 11pm

      3rd:  11pm to 7am

       

      Saturday and Sunday

      1st: 7am to 7pm

      3rd: 7pm to 7am

       

      And this is where I'm running into problems. Tableau doesn't seem to like nested functions too much. So I started attempting to hash out what my code needs to do. First I'll put it in pseudocode:

      If the day is Saturday or Sunday between the hours of 7am or 7pm then it's 1st shift,

      If the day is Monday, Tuesday, Wednesday, Thursday or Friday between the hours of 7am and 3pm it's 1st shift,

      If the day is Monday, Tuesday, Wednesday, Thursday or Friday between the hours of 3pm and 11pm it's 2nd shift,

      The rest of the times are 3rd shift.

       

      So since I wasn't sure exactly how to do this I started off with Saturday. I put together the following bit of code just to see if this method would work:

      IF (([DayofWeek]="Saturday") AND ((DATEPART('hour',[Opened])>=7) OR (DATEPART('hour',[Opened])<19))) THEN "First Shift"

      ELSEIF (([DayofWeek]="Saturday") AND ((DATEPART('hour',[Opened])<7) OR (DATEPART('hour',[Opened])>=19))) THEN "Third Shift"

      ELSE "Other"

      END

       

      Short answer: It doesn't work. When I look at the data itself it's only matching on the day and labeling anything on Saturday as "First Shift". It's essentially ignoring the AND modifier. From what I've read I would think this would work (unless I'm missing some formatting) but it doesn't. So this is where I am. If anyone can point out any mistakes or show me a better way to do any of this that would be great. I'm still new to Tableau and just feeling around.