Many companies use a standardized 4-4-5 calendar for their accounting purposes. In a 4-4-5 calendar, each quarter is broken up into two 4-week periods and one 5-week period to make 13-week quarters. These quarters often start on the first Monday of that period, so the first day of the fiscal year will be the first Monday that occurs after January 1st.

There are a couple of ways to access this type of calendar in Looker. Often, companies have a pre-generated calendar that can be joined into Explores in a model file.

In cases where there is no pre-generated calendar with the appropriate denotations, we can create a 4-4-5 calendar using LookML date dimensions and some logic to assign the appropriate labels for a 4-4-5 calendar to existing date fields. The main advantage to this approach is that we can define when a fiscal year begins on a model-to-model basis by applying the fiscal_month_offset parameter at the model level. More information is available on the fiscal_month_offset documentation page.

The Code

Note: Because we are relying on a 52-week year (which does not completely fit into the 365-day calendar), month 1 will usually be truncated and month 12 will be elongated. The following example is done in a Redshift SQL syntax and will need to be modified accordingly for your database SQL dialect.

Ideally, we want a date series that will look like this, with dimensions for Financial Month (FM), Financial Quarter (FQ), and Financial Year (FY), which we will create below.

The first step in this approach is to create a dimension to identify the first Monday of any quarter, using the following syntax:

# This dimension isolates the first Monday of any quarter. 
# Beware Looker's timeframe casting, as it can cause an issue depending on where you are located!

dimension: created_financial_quarter_first_day {
 group_label: "Created Date"
 type: date
 sql: DATE_TRUNC('w',DATEADD(w,1,DATEADD(day,-1,DATE_TRUNC('qtr', ${created_raw})))) ;;
 }

Starting with creating our Financial Month (FM) dimension, we can define our months by casting our date with the week_of_year timeframe parameter, as described on the Timeframe Options documentation page.
Casting our dates like this gives an integer value from 1 - 52, with our weeks starting on Mondays. By implementing a CASE WHEN statement within the sql parameter of our FM dimension, we can define the logic that will give us our financial months.
In recognition of the above caveats, we also add a condition that will take care of cases when the first week of the calendar year starts during the preceding fiscal year, thus elongating FM 12 and truncating FM 1.


 dimension: 445_financial_month {
   datatype: date
   group_label: "Created Date"
   label: "Financial Month"
   sql:
   CASE WHEN ${order_created_week_of_year} = 1 
   AND ${order_created_raw} < ${created_financial_quarter_first_day} 
   OR ${order_created_week_of_year} = 1
   AND ${order_created_raw} > DATEADD('y',-1,${order_created_raw}) 
           THEN 'FM' || 12
       WHEN ${order_created_week_of_year} between 1 AND 4 THEN 'FM ' || 1
       WHEN ${order_created_week_of_year} between 5 AND 8 THEN 'FM ' || 2
       WHEN ${order_created_week_of_year} between 9 AND 13 THEN 'FM ' || 3
       WHEN ${order_created_week_of_year} between 14 AND 17 THEN 'FM ' || 4
       WHEN ${order_created_week_of_year} between 18 AND 21 THEN 'FM ' || 5
       WHEN ${order_created_week_of_year} between 22 AND 26 THEN 'FM ' || 6
       WHEN ${order_created_week_of_year} between 27 AND 30 THEN 'FM ' || 7
       WHEN ${order_created_week_of_year} between 31 AND 34 THEN 'FM ' || 8
       WHEN ${order_created_week_of_year} between 35 AND 39 THEN 'FM ' || 9
       WHEN ${order_created_week_of_year} between 40 AND 43 THEN 'FM ' || 10
       WHEN ${order_created_week_of_year} between 44 AND 47 THEN 'FM ' || 11
   ELSE 'FM' || 12
   END ;;
 }

Using this same logic, we can create our Financial Quarter (FQ) dimension by casting our quarters as integers and then using a CASE statement to assign the string "FQ", combined with an integer, to indicate which FQ we are in.

dimension: 445_financial_quarter {
  datatype: date
  group_label: "Created Date"
  label: "Financial Quarter"
  sql:
  CASE 
       WHEN ${order_created_week_of_year} = 1 
           AND ${order_created_raw} < ${created_financial_quarter_first_day} 
       OR ${order_created_week_of_year} = 1
           AND ${order_created_raw} > DATEADD('y',-1,${order_created_raw}) 
         THEN 'FQ ' || 4 
       WHEN ${order_created_week_of_year} between 1 AND 13 THEN 'FQ ' || 1
       WHEN ${order_created_week_of_year} between 14 AND 26 THEN 'FQ ' || 2
       WHEN ${order_created_week_of_year} between 27 AND 39 THEN 'FQ ' || 3
   ELSE 'FQ ' || 4
   END ;;
}

Creating the Financial Year (FY) dimension is slightly trickier. Using the system above, we can fall into a trap where the first of the year falls on a day before Monday, while officially the financial (or fiscal) year does not start until the first Monday after the first of the year. In our data table above, January 1st might fall on a Tuesday, but in this system, the FY wouldn't start until the following Monday, January 7th.

Our challenge is to isolate the first few days of the year before the first Monday, then extract the date_part as a year and subtract '1', since these few days officially belong to the previous financial year.

dimension: 445_financial_year {
  group_label: "Created Date"
  label: "Financial Year"
  sql:
  CASE WHEN ${created_raw} < ${created_financial_quarter_first_day}
  AND TO_CHAR(DATE_TRUNC('qtr', ${created_raw}),'Q')::int = 1
  THEN 'FY '|| DATE_PART('year', ${created_raw}) - 1
  ELSE 'FY '|| DATE_PART('year', ${created_raw})
  END ;;
}

Congratulations! You now have a working version of your 4-4-5 calendar in LookML.

Articles in this section

4-4-5 Calendar Using LookML

The Code