1 Reply Latest reply on Apr 18, 2018 1:39 PM by Branden Kornell

    Grouping Days and Hours to Specific Shifts

    Robert Patterson

      Hi Everyone!

       

      I have data with a column called 'ActionDate', I am trying to associate the action date with specific team shifts.  Is it possible to group hours and specific days using a calculated field?  My end goal is to have a stacked bar view showing the number of actions by each team, at a daily pacing.  I've looked at a couple of different forum answers but I can't seem to find any solutions for this specific of a schedule.

       

      Here are the teams I have and their hours:

       

      Team 1:

      Wednesday 10:00 PM - 11:59 PM

      Thursday 12:00 AM - 6:59 AM, 10:00 PM - 11:59 PM

      Friday 12:00 AM - 6:59 AM, 10:00 PM - 11:59 PM

      Saturday All Day

      Sunday All Day

       

      Team 2:

      Monday - Friday 7:00 AM - 9:59 PM

       

      This is the format of my date field looking at the raw data:

      Thanks for any help that can be offered!

        • 1. Re: Grouping Days and Hours to Specific Shifts
          Branden Kornell

          Yes, you can do this with an IF statement, plus Tableau's date functions (especially DATEPART with 'weekday' and 'hour').

           

          For the examples you gave, try

           

          if (datepart('weekday', [ActionDate]) = 4 // Wednesday

              and datepart('hour', [ActionDate]) >= 22) // 10 PM

              or

              (datepart('weekday', [ActionDate]) = 5 // Thursday

              or datepart('weekday', [ActionDate]) = 6) // Friday

              and (datepart('hour', [ActionDate]) < 7  // 6:59 AM

              or datepart('hour', [ActionDate]) >= 22) // 10 PM

              or datepart('weekday', [ActionDate]) = 7 // Saturday

              or datepart('weekday', [ActionDate]) = 1 // Sunday

          then

              'Team 1'

          elseif

              (datepart('weekday', [ActionDate]) >= 2 // Monday

              and datepart('weekday', [ActionDate]) <= 6) // Friday

              and (datepart('hour', [ActionDate]) >= 7 // 7 AM

              or datepart('hour', [ActionDate]) < 22) // 9:59 PM

          then

              'Team 2'

          ELSE   

              'Other'

          END

           

          Important: Keep in mind that you're hard-coding when the teams are working. If teams ever shift hours, you would need to manually go in and change formulas in Tableau. In that case, it may be better to centralize the logic elsewhere.