Set up a custom calendar

When to use a custom calendar

Use a custom calendar when your periods do not start on the first day of a Gregorian month. Examples: a 4-5-4 or 5-4-4 retail calendar, a 13-period calendar, an ISO-week fiscal year, or a broadcast calendar.

Use fiscal_month_offset instead when your fiscal year starts on the first day of a Gregorian month. A custom calendar needs a calendar table in your database, while fiscal_month_offset only shifts the start of the year. A custom calendar replaces the fiscal calendar for the dimensions that use it.

Keep these limits in mind before you choose a custom calendar:

Forecasting does not use the calendar.

Period over period is not available on a query with two date fields, or with a sub-day timeframe.

Date fill needs a date filter with a start and an end, or a named period such as this year.

Requirements

To follow the steps in this guide, you’ll need:

Modeler or Connection Admin permissions for the connection, so you can edit the shared model and run a schema refresh

Permission to create a table in your database. The calendar must be a database table. A data input table (uploaded file) only works if the connection’s Schema for table upload property is defined, which pushes the table to the database.

Create a calendar table

Create a table in your database with one row for each date. Include every date that your data can contain. Dates outside the table get NULL for each calendar timeframe.Each row holds the date and the periods that the date belongs to:

Column	Example	Notes
`calendar_date`	`2024-03-01`	The Gregorian date. Must be unique.
`fy_start_date`	`2024-02-04`	The first date of the retail year
`retail_quarter_start`	`2024-02-04`	The first date of the retail quarter
`retail_month_start`	`2024-02-04`	The first date of the retail month
`retail_week_start`	`2024-02-25`	The first date of the retail week
`fiscal_year_number`	`2024`	The retail year as a number
`retail_quarter_num`	`1`	The quarter number in the year
`retail_month_num`	`1`	The month number in the year
`retail_month_name`	`February`	The month name
`retail_week_num`	`4`	The week number in the year
`day_of_retail_year`	`27`	The day number in the year

Period columns must hold the first date of the period, such as 2024-02-04. Do not use a year number or a label such as Q1. Omni groups, sorts, and compares on that date.Only calendar_date and the columns you plan to map are required. A calendar that maps only quarter needs two columns.

Refresh the schema

Run a schema refresh so the calendar table appears in the model as a view. Note the view name that Omni assigns to the table. The view does not need to be in a topic.

Define the calendar in the model file

Add custom_calendars to the model file. Map each timeframe to a field in the calendar view:

model

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

With year_num mapped, each quarter displays as 2024-Q1. Without it, each quarter displays its first date, such as Feb 4, 2024.

Apply the calendar to a dimension

In a view file, add custom_calendar to each date or timestamp dimension that must use the calendar:

sales.view

dimensions:
  sold_at:
    custom_calendar: retail_454

A custom calendar changes the results of saved content that uses the dimension. A this quarter filter or a period-over-period comparison now uses the calendar’s quarter. Check saved content before you apply a calendar to a dimension.

Check the result

Lastly, test the calendar in a workbook query:

Add the Quarter timeframe of sold_at and a measure. Each row should show a retail quarter, such as 2024-Q1. The first date of each row should match retail_quarter_start in your calendar table.
Add a sold_at filter set to this quarter. The query should return the current retail quarter, not the Gregorian quarter.

When to use a custom calendar

Requirements

Create a calendar table

Refresh the schema

Define the calendar in the model file

Apply the calendar to a dimension

Check the result

Next steps