Using custom calendars in Looker

Preview: This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

If you encounter an issue during the Public Preview of custom calendars, report it to [e-mail].

For Looker connections to Snowflake or Google BigQuery, the custom calendar feature in Looker lets you define a custom calendar in your database, such as a specific fiscal or retail calendar, and then apply the calendar to a date-based dimension group in your LookML model. Your users can then create Explore queries using your custom timeframes, such as custom_week, custom_period, as if they were standard timeframes.

Prerequisites

Before using custom calendars, ensure the following prerequisites are met:

• You must have a Looker connection to Snowflake or Google BigQuery.
• You must create a calendar table in your database that you can then model as a view in Looker. Looker uses the calendar table in your database to calculate custom timeframes. See the Create a calendar table in your database section for more information.
• Your LookML project must use the new LookML runtime. If the Use Legacy LookML Runtime legacy feature is enabled on your instance, you must add the new_lookml_runtime: yes statement to your project's manifest file.

Creating a custom calendar

To implement a custom calendar, you need to perform these general steps that are described in the following sections:

1. Create your calendar table in your database
2. Define your custom calendar view in LookML
3. Create a custom calendar dimension group

Create a calendar table in your database

To calculate dates for custom calendars, Looker needs a dedicated calendar table in your database that defines your custom timeframes. Your table must have a reference date column that uses the standard calendar date. Looker joins your calendar table to your data tables (such as an orders table) based on the reference date column.

The column names in the calendar table are flexible. When you define the custom calendar view in LookML, you will use the calendar_definition block to map the columns in your database to standard custom timeframe names.

The following is an example table schema for a calendar table named fiscal_calendar_table.

Column Name	Data Type	Description
reference_date	DATE	The standard calendar date (such as '2023-01-01'). Used for joining. Should be a unique or primary key.
fiscal_year	VARCHAR	The fiscal year (such as 'FY2023').
fiscal_year_num	INTEGER	The numeric fiscal year (such as 2023).
fiscal_quarter_of_year	VARCHAR	The fiscal quarter (such as 'FQ1').
fiscal_quarter_of_year_num	INTEGER	The numeric fiscal quarter (such as 1).
fiscal_week_of_year	VARCHAR	The fiscal week of the year (such as 'Week01', 'FW01').
fiscal_week_of_year_num	INTEGER	The numeric fiscal week of the year (such as 1).
fiscal_period_of_year	VARCHAR	A custom period name (such as 'P01').
fiscal_period_of_year_num	INTEGER	The numeric custom period (such as 1).
season	VARCHAR	A custom season name (such as 'Winter').
season_num	INTEGER	The numeric custom season (such as 1).

To illustrate, here are some sample rows from the fiscal_calendar_table table:

reference_date	fiscal_year	fiscal_year_num	fiscal_period	fiscal_period_num
2023-12-25	FY2024	2024	P01	1
2023-12-26	FY2024	2024	P01	1
2024-01-01	FY2024	2024	P02	2
2024-01-02	FY2024	2024	P02	2

Define your custom calendar view in LookML

After you have created a calendar table in your database, you need to create a LookML view to model the database calendar table.

Tip: The easiest way to create the view file is to use the Create View From Table option in the Looker IDE to have Looker automatically model the fields of your table. After Looker creates the view file, you can then add the calendar_definition parameter block, modify any of the automatically-created dimensions, and add any other dimension definitions you need.

The custom calendar view file must include the following:

• The sql_table_name parameter that points to the custom calendar table in your database.
• The calendar_definition block to map your table's columns to the Looker custom timeframe types.
• dimension parameters to model the columns of the custom calendar table in your database.

Here is an example view file called fiscal_calendar.view.lkml that models the example fiscal_calendar_table:

view: fiscal_calendar {
  sql_table_name: fiscal_calendar_table ;;
  calendar_definition: {
    reference_date: reference_date
    timeframe_mapping: {
      custom_year: fiscal_year
      custom_quarter: fiscal_quarter_of_year
      custom_date: fiscal_date
      custom_week: fiscal_week_of_year
      custom_period: fiscal_period_of_year
      custom_season: season
    }
    timeframe_ordinal_mapping: {
      custom_year: fiscal_year_num
      custom_quarter: fiscal_quarter_of_year_num
      custom_date: fiscal_date_num
      custom_week: fiscal_week_of_year_num
      custom_period: fiscal_period_of_year_num
      custom_season: season_num
    }
  }

  dimension: reference_date {
    type: date
    primary_key: yes
    sql: ${TABLE}.reference_date ;; # Assuming column name is reference_date
  }

  dimension: fiscal_date {
  type: string
  sql: FORMAT_TIMESTAMP('%Y-%m-%d', ${TABLE}.reference_date) ;;
}

  dimension: fiscal_year {
    type: string
    sql: ${TABLE}.fiscal_year ;;
  }

  dimension: fiscal_year_num {
    type: number
    sql: ${TABLE}.fiscal_year_num ;;
  }

  # ... other dimensions for quarters, weeks, periods, seasons, etc. ...

  # Example placeholder dimensions for unused timeframes
  dimension: season {
    type: string
    sql: 'N/A' ;;
    hidden: yes
  }
  dimension: season_num {
    type: number
    sql: 0 ;;
    hidden: yes
  }
}

See the calendar_definition parameter page for details on the calendar_definition parameters.

Create a custom calendar dimension group

After you have created a calendar table in your database and modeled the database calendar table in LookML, you can create a dimension group of type: custom_calendar that is based on the custom calendar view.

For example, here is a view filed named orders.view.lkml that defines a custom calendar dimension group.

include: "/views/fiscal_calendar.view"

view: orders {
  sql_table_name: public.orders ;;

  dimension_group: created {
    type: custom_calendar

    # Optional list of allowed timeframes
    custom_timeframes: [
      custom_date,
      custom_week,
      custom_year
    ]
    sql: ${TABLE}.created_at ;;
    based_on_calendar: fiscal_calendar  # This links to your calendar view
  }
}

With this LookML, Looker will automatically create a new set of custom timeframes for the created dimension group (such as "Created Custom Year", "Created Custom Week"). Users can then use these fields for exploring, reporting, and filtering.

See the dimension_group documentation page for the details on creating a dimension group for custom calendars.

Things to consider

Note the following limitations with custom calendars:

• Filtered measures: Custom calendars aren't supported for filtered measures: You can't reference a custom calendar dimension in the filters parameter of a measure.
• Advanced filters: Custom calendars aren't supported in advanced filters: You can't reference a custom calendar dimension in Looker filter expressions.
• Symmetric aggregates: In some complex join scenarios, symmetric aggregates may not be fully supported for queries involving custom calendar dimensions.
• Dimension fill: Looker cannot dimension fill missing dates for custom calendar dimensions.
• Filtering beyond calendar boundaries: If a filter is applied to a custom timeframe that results in a date that is not in the calendar then unexpected results may be returned. This has to do with the ordinal arithmetic that is used to calculate the beginning of a period.
• Timezone conversion behavior: Looker honors the existing timezone conversion semantics. That is, if the database and query timezone match then no timezone conversion is applied. If they disagree, then Looker applies the timezone conversion. You can override this behavior by specifying convert_tz: no to your custom calendar dimension group.
• Default sorting: Looker applies a default sort to the first date field when custom calendar fields are selected. The user can then specify different data sorting.