0 Replies Latest reply on May 13, 2016 4:22 PM by Shinichiro Murakami

    Problem by 5-4-4 Calendar ??

    Shinichiro Murakami

      All,

       

      Not a small number of people in the community are using 5-4-4 (or 4-4-5, 4-5-4 whatever) calendar.

       

      Pros :

      easy to compare quarter to quarter, because quarter is consist of 13 weeks (excluding adjustment every 6 years)

       

      Cons :

      Every quarter, we have 5 weeks' month and "month to month" comparison is always difficult.

      We cannot use Tableau's calendar function with fiscal calendar. 

      Also there was no easy way to convert "calendar date" into Fiscal month, quarter etc.

       

      I was struggling of this conversion, but finally created common solutions.

       

      Given assumption

      Fiscal year starts from around 9/1 every year.

      Q1 = Sep~Nov  (5-4-4 week respectively) = 13 weeks

      Q2 = Dec ~ Feb (5-4-4 week respectively) = 13 weeks

      Q3 = Mar ~ May (5-4-4 week respectively) = 13 weeks

      Q4 = Jun ~ Aug (5-4-4 week respectively) = 13 weeks

      Adjustment happens irregularly.

      But always in November. If adjustment happens, November becomes 5 week month and Q1 becomes 14 week quarter.

      Workweek starts from Friday.

      WW01 is defined as below

      if Friday >=Jan 3rd, then that week becomes WW01.

      But WW01 is not always Fiscal January.

      In other words, workweek and fiscal year/month/quarter is independent.

       

      I will use "yyyy/mm/dd" format for better understandings.

       

      I will try to explain the logic step by step.  I hope these are some sort of universal logic for 5-4-4 calendar.

       

      1. Pick any fiscal year start date as base date. The date you will not go back before that date.

      In my case picked 2009/9/3.

       

      2. Specify/find the month adjustment happens (around every 6 years).

      In my case, November of year 2014,2019, 2025, 2031, and 2036 between 2010 thru 2040.

       

      3. Calculate Days from base date(2009/9/4) with adjusting 7 days on every above date.

       

      [Days from 2009/9/4] //

      [Date]

      + (if [Date] >=date("2014/11/7") then -7 else 0 end)

      + (if [Date] >=date("2019/11/8") then -7 else 0 end)

      + (if [Date] >=date("2025/11/7") then -7 else 0 end)

      + (if [Date] >=date("2031/11/7") then -7 else 0 end)

      + (if [Date] >=date("2036/11/7") then -7 else 0 end)

      -date("2009/9/4")

       

       

       

      4. Calculate Quarter first, because the "days" in quarter in "adjusted date" is always 91 days.

       

      [Fiscal Quarter]  //  Returning 1 to 4.

      int([Days from 2009/9/4]/91)%4+1

       

      5. Calculate "month" based on the date from the first date of Quarter.

       

      [Fiscal Month]  //  this is most complicated, but the key is counting "days in quarter".

      ((int(

      [Days from 2009/9/4]/91)%4+1

      )*3+4

      +(if (([Days from 2009/9/4])%91+1) <=35 then 1

      elseif (([Days from 2009/9/4])%91+1) <=63 then 2

      else 3 END))

      %12

      +1

       

      6. Workweek calculation is completely independent from month/quarter/year.

       

      [workweek] // WW starts from Friday.  //  December 29,30,31 can be workweek 01, then three days offset is considered.

      int(

      (datetrunc('week',[Date],'Friday')+3-

      datetrunc('year',datetrunc('week',[Date],'Friday')+3))

      /7

      )+1

       

      https://public.tableau.com/static/images/Fi/Fiscal_Calendar9_2/Calendar_view/1.png

       

       

      Thanks,

      Shin