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

# Problem by 5-4-4 Calendar ??

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.

### 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

Thanks,

Shin