1 Reply Latest reply on Dec 14, 2012 8:56 AM by Mark Holtz

    What's the best way to create a time dimension with 5/4/4 week periods?

    ruud Hasselerharm

      I want a time dimension with quarters based on 5+4+4 weeks (4 quarters of 13 week is 52 weeks). Every week starts on saturday, so the first day of my year also starts ons saturday. What would be the best way to solver this in Tableau? Or would it be easier to use a timedimension table (made in excel  for example) with all the the date/week/quarter/year relations?

        • 1. Re: What's the best way to create a time dimension with 5/4/4 week periods?
          Mark Holtz

          Not sure I understand what you are after here...Do you want the dimension to give 4 distinct values of Q1, Q2, Q3 and Q4? If so, Tableau supports that innately with its Date dimension architecture.

           

          If you want 13 distinct values (which would divide the year into 13 28-day-periods with 1 day left over which we'll lump into the end), I'd create a field with something like:
          IF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 28 THEN '28dperiod01'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 56 THEN '28dperiod02'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 84 THEN '28dperiod03'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 112 THEN '28dperiod04'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 140 THEN '28dperiod05'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 168 THEN '28dperiod06'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 196 THEN '28dperiod07'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 224 THEN '28dperiod08'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 252 THEN '28dperiod09'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 280 THEN '28dperiod10'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 308 THEN '28dperiod11'
          ELSEIF DATEDIFF('day',DATETRUNC('year',[DateField]),[DateField]) < 336 THEN '28dperiod12'
          ELSE '28dperiod13'
          END