custom_calendars - Model files

Defines calendars with your own year, quarter, month, and week periods. Omni reads the periods from a calendar table in your database. A common example is the retail 4-5-4 calendar. A dimension uses a calendar through its custom_calendar parameter. For a walkthrough, refer to Set up a custom calendar.

A custom calendar replaces the fiscal calendar for the dimensions that use it. fiscal_month_offset and the fiscal_* timeframes are not available on those dimensions.

Syntax

custom_calendars:
  <calendar_name>:
    view: <view_name>
    date: <field_name>
    mappings:
      <timeframe_name>: <field_name>
    ignored: true/false

Properties

object

A map of custom calendar objects. Each key is the name of a calendar. Calendar names must be unique.

Hide custom_calendars properties

object

required

The name of the calendar. Dimensions use this name in their custom_calendar parameter.

Hide calendar_name properties

string

required

The view that holds the calendar table. The table must have one row for each date. Dates outside the table get NULL for each mapped timeframe.The view cannot be a data input table (uploaded file) unless the view has been pushed to the database. This occurs when the connection’s Schema for table upload property is defined.

string

default:"date"

The field in view that holds the Gregorian date. The field must be a date or timestamp dimension.

object

The timeframes that the calendar defines, and the field in view that holds each one. Omitted timeframes use the Gregorian calendar.

Timeframe	Field holds
`year`	The first date of the year
`quarter`	The first date of the quarter
`month`	The first date of the month
`week`	The first date of the week. A mapped `week` ignores `week_start_day`.
`quarter_of_year`	The quarter number, 1 to 4
`month_num`	The month number, 1 to 12
`month_name`	The month name
`week_of_year`	The week number in the year
`day_of_year`	The day number in the year
`day_of_quarter`	The day number in the quarter
`day_of_month`	The day number in the month
`year_num`	The year number

boolean

default:"false"

Set to true in an extending model to remove a calendar from the parent model. Dimensions that use an ignored calendar show a validation error.

Query behavior

When a dimension uses a calendar, Omni joins the calendar view on the date. All timeframes of the dimension share one join. The calendar view does not need to be in the topic, and the join does not cause fan-out.

How periods display

A mapped year, quarter, or month displays the first date of the period, such as Feb 4, 2024. To display 2024, 2024-Q1, or 2024-02 instead, also map year_num, plus quarter_of_year for quarters or month_num for months:

Timeframe	Displays	Requires
`year`	`2024`	`year_num`
`quarter`	`2024-Q1`	`year_num` and `quarter_of_year`
`month`	`2024-02`	`year_num` and `month_num`

These values are strings, so the dimension’s format does not apply. week always displays the first date of the week.

A mapped week ignores week_start_day.

Grouping and sorting

A mapped year, quarter, month, or week groups by the calendar period. Rows sort by the first date of the period. Pivots and drills group by the same calendar period.

Filters

A filter uses the calendar when its value names a period that the calendar maps. A filter that names a specific date uses the Gregorian calendar, because a date means the same thing on every calendar.

Filter value	Calendar used	Example
A relative period	Custom	`this quarter`, `2 quarters ago`, `last 12 weeks`
A specific period	Custom	`2024`, `2024 Q3`, `2024-04`
A date or a count of days	Gregorian	`on or after 2024-05-01`, `yesterday`, `last 7 days`

Filters on a specific period need year_num in the calendar mappings, plus quarter_of_year for quarters or month_num for months. Some filter values have no meaning on a custom calendar and return an error. For example, this Q3 and last january name a Gregorian month or quarter, and a fiscal_* filter names the fiscal calendar that the custom calendar replaces.

Date fill

Date fill on a mapped timeframe adds the calendar’s periods. The query needs a date filter with a start and an end, or a named period such as this year.

Period over period

Period-over-period comparisons in workbooks and dashboard controls compare calendar periods. For example, “previous quarter” on a retail calendar means the previous retail quarter, not the previous Gregorian quarter. Two rules apply:

The date filter must name a period, such as this quarter or in the year 2024. A filter on specific dates does not work.
The comparison must be a whole number of that period. On a quarter filter, 1 year compares against four quarters ago, but 1 month does not work.

Examples

Retail 4-5-4 calendar

custom_calendars:
  retail_454:
    view: retail_calendar
    date: calendar_date
    mappings:
      year: fy_start_date
      quarter: retail_quarter_start
      month: retail_month_start
      week: retail_week_start
      year_num: fiscal_year_number
      quarter_of_year: retail_quarter_num
      month_num: retail_month_num
      month_name: retail_month_name
      week_of_year: retail_week_num
      day_of_year: day_of_retail_year

Without year_num, quarters display their first date, and filters on a specific quarter such as 2024 Q3 are not available.

Quarter only

custom_calendars:
  retail_quarters:
    view: retail_calendar
    date: calendar_date
    mappings:
      quarter: retail_quarter_start

Getting started

Manage content

Analyze & build

Visualize & present

Deliver content

Model data

Manage integrations

custom_calendars - Model files

Syntax

Properties

Query behavior

How periods display

Grouping and sorting

Filters

Date fill

Period over period

Examples

​Syntax

​Properties

​Query behavior

​How periods display

​Grouping and sorting

​Filters

​Date fill

​Period over period

​Examples

Syntax

Properties

Query behavior

How periods display

Grouping and sorting

Filters

Date fill

Period over period

Examples