> ## Documentation Index
> Fetch the complete documentation index at: https://cubed3-cube-calendar-preagg-wrong-results.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Calendar cubes

> Defines calendar cubes from dedicated calendar tables to drive custom fiscal or retail time shifts and non-default time granularities where Tesseract is enabled.

*Calendar cubes* are used to implement custom calendars, such as retail calendars.
If your data model contains a calendar table, it can be modeled as a calendar cube.

Calendar cubes can be used to [override](#overriding-time-shifts) the default time
shift behavior of time-shift measures as well as [override](#overriding-granularities)
the default granularities of time dimensions.

<Warning>
  Calendar cubes are powered by Tesseract, the [next-generation data modeling
  engine][link-tesseract]. In versions before v1.7.0, it was not enabled by default.
</Warning>

## Configuration

Calendar cubes are [cubes][ref-cubes] where the [`calendar` parameter][ref-cubes-calendar]
is set to `true`. This indicates that the cube is a calendar cube and allows the use of
custom time shifts and granularities.

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: fiscal_calendar
      calendar: true
      sql: >
        SELECT
          calendar_date,
          start_of_week, start_of_month, start_of_year,
          week_ago, month_ago, year_ago
        FROM calendar_table
      
      dimensions:
        - name: date
          sql: calendar_date
          type: time
          primary_key: true

          time_shift:
            - type: prior
              interval: 1 week
              sql: "{CUBE}.week_ago"

            - type: prior
              interval: 1 month
              sql: "{CUBE}.month_ago"

            - type: prior
              interval: 1 year
              sql: "{CUBE}.year_ago"
          
          granularities:
            - name: week
              sql: "{CUBE}.start_of_week"
              
            - name: month
              sql: "{CUBE}.start_of_month"
              
            - name: year
              sql: "{CUBE}.start_of_year"
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`fiscal_calendar`, {
    calendar: true,
    sql: `
      SELECT
        calendar_date,
        start_of_week, start_of_month, start_of_year,
        week_ago, month_ago, year_ago
      FROM calendar_table
    `,

    dimensions: {
      date: {
        sql: `calendar_date`,
        type: `time`,
        primary_key: true,

        time_shift: [
          { type: `prior`, interval: `1 week`, sql: `${CUBE}.week_ago` },
          { type: `prior`, interval: `1 month`, sql: `${CUBE}.month_ago` },
          { type: `prior`, interval: `1 year`, sql: `${CUBE}.year_ago` }
        ],

        granularities: {
          week: { sql: `${CUBE}.start_of_week` },
          month: { sql: `${CUBE}.start_of_month` },
          year: { sql: `${CUBE}.start_of_year` }
        }
      }
    }
  })
  ```
</CodeGroup>

<Warning>
  A calendar cube must have exactly one [`primary_key`][ref-primary-key] dimension. A second
  one compiles without error but makes every query that references the cube fail with
  `Cube '...' has multiple primary keys, but only one is allowed for calendar cubes`.
</Warning>

### Joins

Calendar cubes are only useful when they are joined with other cubes in the data model.

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: sales
      sql_table: sales_facts

      joins:
        - name: fiscal_calendar
          sql: "{CUBE}.date = {fiscal_calendar.date}"
          relationship: many_to_one
      
      # ...
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`sales`, {
    sql_table: `sales_facts`,

    joins: {
      fiscal_calendar: {
        sql: `${CUBE}.date = ${fiscal_calendar.date}`,
        relationship: `many_to_one`
      }
    },

    // ...
  })
  ```
</CodeGroup>

When joining a calendar cube to other cubes, the following requirements must be met:

* The calendar cube's join dimension must be of [type `time`][ref-time-dimension] and
  also be the [`primary_key`][ref-primary-key].
* The other cube's join dimension must also be of [type `time`][ref-time-dimension].

## Overriding time shifts

Calendar cubes can be used to override the default time shift behavior of [time-shift
measures][ref-time-shift]. It can help implement custom time shifts or reuse common time
shifts across multiple cubes.

By default, a time shift like `prior` + `1 month` will add `INTERVAL '1 month'` to the
time dimension value in the generated SQL. However, with custom calendars, a more nuanced
approach is often needed, such as mapping each date to another pre-calculated date from
the calendar table.

In the following example, the `custom_calendar` cube defines a custom time shift for
`prior` + `1 month` that uses the `month_ago` column from the calendar table. It also
defines a custom time shift `my_favorite_time_shift` of type `prior` + the `42 days`
interval.

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: custom_calendar
      calendar: true
      sql: >
        SELECT '2025-01-01' AS date, '2024-12-15' AS month_ago UNION ALL
        SELECT '2025-02-01' AS date, '2025-01-15' AS month_ago UNION ALL
        SELECT '2025-03-01' AS date, '2025-02-15' AS month_ago UNION ALL
        SELECT '2025-04-01' AS date, '2025-03-15' AS month_ago UNION ALL
        SELECT '2025-05-01' AS date, '2025-04-15' AS month_ago UNION ALL
        SELECT '2025-06-01' AS date, '2025-05-15' AS month_ago
      
      dimensions:
        - name: date
          sql: "{CUBE}.date::TIMESTAMP"
          type: time
          primary_key: true

          time_shift:
            - type: prior
              interval: 1 month
              sql: "{CUBE}.month_ago::TIMESTAMP"
          
            - type: prior
              interval: 42 days
              name: my_favorite_time_shift

    - name: sales
      sql: >
        SELECT 1 AS id, 101 AS amount, '2025-01-01'::TIMESTAMP AS date UNION ALL
        SELECT 2 AS id, 202 AS amount, '2025-02-01'::TIMESTAMP AS date UNION ALL
        SELECT 3 AS id, 303 AS amount, '2025-03-01'::TIMESTAMP AS date UNION ALL
        SELECT 4 AS id, 404 AS amount, '2025-04-01'::TIMESTAMP AS date UNION ALL
        SELECT 5 AS id, 505 AS amount, '2025-05-01'::TIMESTAMP AS date UNION ALL
        SELECT 6 AS id, 606 AS amount, '2025-06-01'::TIMESTAMP AS date

      joins:
        - name: custom_calendar
          sql: "{CUBE}.date = {custom_calendar.date}"
          relationship: many_to_one

      dimensions:
        - name: id
          sql: id
          type: number
          primary_key: true

      measures:
        - name: total_sales
          sql: amount
          type: sum

        - name: total_sales_prior_month
          sql: "{total_sales}"
          type: number
          multi_stage: true
          time_shift:
            - type: prior
              interval: 1 month

        - name: total_sales_few_days_ago
          sql: "{total_sales}"
          type: number
          multi_stage: true
          time_shift:
            - name: my_favorite_time_shift
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`custom_calendar`, {
    calendar: true,
    sql: `
      SELECT '2025-01-01' AS date, '2024-12-15' AS month_ago UNION ALL
      SELECT '2025-02-01' AS date, '2025-01-15' AS month_ago UNION ALL
      SELECT '2025-03-01' AS date, '2025-02-15' AS month_ago UNION ALL
      SELECT '2025-04-01' AS date, '2025-03-15' AS month_ago UNION ALL
      SELECT '2025-05-01' AS date, '2025-04-15' AS month_ago UNION ALL
      SELECT '2025-06-01' AS date, '2025-05-15' AS month_ago
    `,

    dimensions: {
      date: {
        sql: `${CUBE}.date::TIMESTAMP`,
        type: `time`,
        primary_key: true,

        time_shift: [
          { type: `prior`, interval: `1 month`, sql: `${CUBE}.month_ago::TIMESTAMP` },
          { type: `prior`, interval: `42 days`, name: `my_favorite_time_shift` }
        ]
      }
    }
  })

  cube(`sales`, {
    sql: `
      SELECT 1 AS id, 101 AS amount, '2025-01-01'::TIMESTAMP AS date UNION ALL
      SELECT 2 AS id, 202 AS amount, '2025-02-01'::TIMESTAMP AS date UNION ALL
      SELECT 3 AS id, 303 AS amount, '2025-03-01'::TIMESTAMP AS date UNION ALL
      SELECT 4 AS id, 404 AS amount, '2025-04-01'::TIMESTAMP AS date UNION ALL
      SELECT 5 AS id, 505 AS amount, '2025-05-01'::TIMESTAMP AS date UNION ALL
      SELECT 6 AS id, 606 AS amount, '2025-06-01'::TIMESTAMP AS date
    `,

    joins: {
      custom_calendar: {
        sql: `${CUBE}.date = ${custom_calendar.date}`,
        relationship: `many_to_one`
      }
    },

    dimensions: {
      id: {
        sql: `id`,
        type: `number`,
        primary_key: true
      }
    },

    measures: {
      total_sales: {
        sql: `amount`,
        type: `sum`
      },

      total_sales_prior_month: {
        sql: `${total_sales}`,
        type: `number`,
        multi_stage: true,
        time_shift: [
          { type: `prior`, interval: `1 month` }
        ]
      },

      total_sales_few_days_ago: {
        sql: `${total_sales}`,
        type: `number`,
        multi_stage: true,
        time_shift: [
          { name: `my_favorite_time_shift` }
        ]
      }
    }
  })
  ```
</CodeGroup>

When the `sales.total_sales_prior_month` and `sales.total_sales_few_days_ago` measures are
queried together with the `custom_calendar.date` time dimension, the generated SQL uses the
custom time shifts defined in the `custom_calendar` cube: one with the `month_ago`
column and another with `INTERVAL '42 days'`.

## Overriding granularities

Calendar cubes can be used to override the default [granularities][ref-granularities] of
[time dimensions][ref-time-dimension].

By default, SQL functions like `DATE_TRUNC` are used to calculate default granularities,
such as `day`, `month`, or `year`. However, custom calendars often have different
definitions for these periods, e.g., a retail calendar might use 4-5-4 week patterns.

Calendar cubes allow you to define custom SQL expressions for each granularity.
In the following example, the `custom_calendar` cube overrides the default `month`
granularity with a pre-calculated `mid_month` column:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: custom_calendar
      calendar: true
      sql: >
        SELECT '2025-01-02' AS date, '2025-01-15' AS mid_month UNION ALL
        SELECT '2025-02-04' AS date, '2025-02-15' AS mid_month UNION ALL
        SELECT '2025-03-09' AS date, '2025-03-15' AS mid_month UNION ALL
        SELECT '2025-04-17' AS date, '2025-04-15' AS mid_month UNION ALL
        SELECT '2025-05-21' AS date, '2025-05-15' AS mid_month UNION ALL
        SELECT '2025-06-30' AS date, '2025-06-15' AS mid_month
      
      dimensions:
        - name: date
          sql: "{CUBE}.date::TIMESTAMP"
          type: time
          primary_key: true

          granularities:
            - name: month
              sql: "{CUBE}.mid_month::TIMESTAMP"

    - name: sales
      sql: >
        SELECT 1 AS id, 101 AS amount, '2025-01-02'::TIMESTAMP AS date UNION ALL
        SELECT 2 AS id, 202 AS amount, '2025-02-04'::TIMESTAMP AS date UNION ALL
        SELECT 3 AS id, 303 AS amount, '2025-03-09'::TIMESTAMP AS date UNION ALL
        SELECT 4 AS id, 404 AS amount, '2025-04-17'::TIMESTAMP AS date UNION ALL
        SELECT 5 AS id, 505 AS amount, '2025-05-21'::TIMESTAMP AS date UNION ALL
        SELECT 6 AS id, 606 AS amount, '2025-06-30'::TIMESTAMP AS date

      joins:
        - name: custom_calendar
          sql: "{CUBE}.date = {custom_calendar.date}"
          relationship: many_to_one

      dimensions:
        - name: id
          sql: id
          type: number
          primary_key: true

      measures:
        - name: revenue
          sql: amount
          type: sum
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`custom_calendar`, {
    calendar: true,
    sql: `
      SELECT '2025-01-02' AS date, '2025-01-15' AS mid_month UNION ALL
      SELECT '2025-02-04' AS date, '2025-02-15' AS mid_month UNION ALL
      SELECT '2025-03-09' AS date, '2025-03-15' AS mid_month UNION ALL
      SELECT '2025-04-17' AS date, '2025-04-15' AS mid_month UNION ALL
      SELECT '2025-05-21' AS date, '2025-05-15' AS mid_month UNION ALL
      SELECT '2025-06-30' AS date, '2025-06-15' AS mid_month
    `,

    dimensions: {
      date: {
        sql: `${CUBE}.date::TIMESTAMP`,
        type: `time`,
        primary_key: true,

        granularities: {
          month: { sql: `${CUBE}.mid_month::TIMESTAMP` }
        }
      }
    }
  })

  cube(`sales`, {
    sql: `
      SELECT 1 AS id, 101 AS amount, '2025-01-02'::TIMESTAMP AS date UNION ALL
      SELECT 2 AS id, 202 AS amount, '2025-02-04'::TIMESTAMP AS date UNION ALL
      SELECT 3 AS id, 303 AS amount, '2025-03-09'::TIMESTAMP AS date UNION ALL
      SELECT 4 AS id, 404 AS amount, '2025-04-17'::TIMESTAMP AS date UNION ALL
      SELECT 5 AS id, 505 AS amount, '2025-05-21'::TIMESTAMP AS date UNION ALL
      SELECT 6 AS id, 606 AS amount, '2025-06-30'::TIMESTAMP AS date
    `,

    joins: {
      custom_calendar: {
        sql: `${CUBE}.date = ${custom_calendar.date}`,
        relationship: `many_to_one`
      }
    },

    dimensions: {
      id: {
        sql: `id`,
        type: `number`,
        primary_key: true
      }
    },

    measures: {
      revenue: {
        sql: `amount`,
        type: `sum`
      }
    }
  })
  ```
</CodeGroup>

When querying `sales.revenue` by `custom_calendar.date` with monthly granularity, the
`mid_month` column will be used instead of the standard `DATE_TRUNC('month', date)`
expression in the generated SQL.

<Warning>
  **A [pre-aggregation][ref-pre-aggregations] must declare the overridden granularity
  itself**, keep it at that one granularity, and set
  [`allow_non_strict_date_range_match`][ref-non-strict] to answer queries carrying a date
  range. A rollup at a finer granularity is refused: custom periods cannot be assembled
  from predefined ones.

  Cube cannot check that a range aligns with the calendar's periods, so with that setting
  on, make sure the ranges you query do.
</Warning>

### Pre-aggregations and time shifts

A query using a [time shift][ref-time-shift] declared on a calendar dimension is served
from pre-aggregations only when everything the shift reads is materialized. Otherwise it
falls back to the source database — results stay correct, acceleration is lost.

Give the calendar its own rollup and tie it to the fact rollup with a
[`rollup_join`][ref-rollup-join]:

```yaml theme={"dark"}
cubes:
  - name: custom_calendar
    calendar: true
    dimensions:
      - name: date_key
        sql: "{CUBE}.date_val"
        type: time
        primary_key: true
        time_shift:
          # A member reference, not `{CUBE}.prev_year_date`.
          - interval: 1 year
            type: prior
            sql: "{CUBE.prev_year_date}"
      - name: prev_year_date
        sql: "{CUBE}.prev_year_date"
        type: time
      - name: retail_date
        sql: "{CUBE}.date_val"
        type: time
        time_shift:
          - interval: 1 year
            type: prior
            sql: "{CUBE.prev_year_date}"
    measures:
      - name: count
        type: count
    pre_aggregations:
      - name: calendar_rollup
        measures: [count]
        dimensions: [prev_year_date]        # the column the shift maps through
        time_dimensions:
          - dimension: date_key
            granularity: day
          - dimension: retail_date
            granularity: day
        indexes:
          - name: date_key_idx
            columns: [date_key]
          - name: prev_year_date_idx
            columns: [prev_year_date]

  - name: sales
    measures:
      - name: revenue
        sql: amount
        type: sum
    dimensions:
      - name: date
        sql: "{CUBE}.date"
        type: time
    joins:
      - name: custom_calendar
        # Members on both sides, not `{CUBE}.date`.
        sql: "{CUBE.date} = {custom_calendar.date_key}"
        relationship: many_to_one
    pre_aggregations:
      - name: sales_rollup
        measures: [revenue]
        time_dimension: date
        granularity: day
        indexes:
          - name: date_idx
            columns: [date]
      - name: sales_by_calendar
        type: rollup_join
        measures: [revenue]
        # The mapped column has to be declared here, not only on the rollup
        # below: this is the member list that decides whether the shift can be
        # served. `rollup_join` takes a single `time_dimension`.
        dimensions: [custom_calendar.prev_year_date]
        time_dimension: custom_calendar.retail_date
        granularity: day
        rollups:
          - sales.sales_rollup
          - custom_calendar.calendar_rollup
```

Six things are required, and missing any of them returns you to the fallback:

* the shift is **declared on the dimension the query groups by**, not only on the primary
  key — otherwise the interval is applied to the key and the mapping is skipped;
* the shift's `sql` is a **member reference**, so a stored column can stand for it;
* the column the shift maps through is **declared on the `rollup_join`** and stored by the
  calendar's rollup — the first is what decides whether the shift can be served;
* the join condition names **members on both sides**, and on the calendar's side the
  **primary key** — that is the member a shift replaces;
* every calendar dimension the query groups by is **declared in both rollups**;
* each rollup has an [index][ref-rollup-join] covering its side of the join key —
  Cube Store joins rollups only on an indexed column.

Reporting by a fiscal week or period works the same way: the join key and the reporting
label are different columns and need not share a grain. Keep the primary key at `day`, so
the shifted join still matches an exact value, and store the period alongside it:

```yaml theme={"dark"}
      - name: calendar_rollup
        measures: [count]
        dimensions: [prev_year_date]
        time_dimensions:
          - dimension: date_key        # the join key, an exact value
            granularity: day
          - dimension: retail_date     # the reporting label
            granularity: week
```

The `rollup_join` then declares `time_dimension: custom_calendar.retail_date` with
`granularity: week`, and — as with any `sql` granularity — needs
[`allow_non_strict_date_range_match`][ref-non-strict], since Cube cannot check that a
range falls on those periods.

### Naming a granularity defined with `sql`

A granularity defined with `sql` must be named after a default granularity: `second`,
`minute`, `hour`, `day`, `week`, `month`, `quarter`, or `year`. Names are matched
case-insensitively. The `sql` parameter changes what an existing unit of time means; it
cannot introduce a new one. A granularity under a name of your own, such as `fiscal_week`,
does not compile:

```
dimensions.<dimension>.granularities.fiscal_week: a granularity defined with 'sql'
must be named after one of the predefined granularities (day, hour, minute, month,
quarter, second, week, year). Define 'fiscal_week' with 'interval' instead
```

The reason is that `interval` is what places a granularity in the `day` → `week` →
`month` → `quarter` → `year` hierarchy, so Cube knows what to roll it up from and what to
decompose it into. A default granularity brings that position with it; a name of your own
does not, so it has to state its `interval`.

The two styles serve different purposes:

|                             | `interval` with `offset` or `origin`            | `sql`                             |
| --------------------------- | ----------------------------------------------- | --------------------------------- |
| **Name**                    | Any name of your own                            | A default granularity only        |
| **Definition**              | Derived arithmetically from a fixed-length unit | Read from a pre-calculated column |
| **Variable-length periods** | Not supported                                   | Supported                         |

Use `interval` to add a granularity, for example a fiscal week of a regular seven days.
See the [custom granularity recipe][ref-recipe-custom-granularity] for `fiscal_week`,
`fiscal_quarter`, and `fiscal_year` defined that way. Use `sql` when a period varies in
length and cannot be derived arithmetically, such as the months and quarters of a 4-5-4
retail calendar. The [custom calendar recipe][ref-recipe-custom-calendar] models a 4-5-4
calendar in full.

[ref-time-shift]: /docs/data-modeling/measures#time-shift

[ref-time-dimension]: /docs/data-modeling/dimensions#time-dimensions

[ref-granularities]: /reference/data-modeling/dimensions#granularities

[ref-cubes]: /reference/data-modeling/cube

[ref-cubes-calendar]: /reference/data-modeling/cube#calendar

[link-tesseract]: https://cube.dev/blog/introducing-next-generation-data-modeling-engine

[ref-recipe-custom-granularity]: /recipes/data-modeling/custom-granularity

[ref-recipe-custom-calendar]: /recipes/data-modeling/custom-calendar

[ref-pre-aggregations]: /docs/pre-aggregations/matching-pre-aggregations

[ref-non-strict]: /reference/data-modeling/pre-aggregations#allow_non_strict_date_range_match

[ref-rollup-join]: /reference/data-modeling/pre-aggregations#rollup_join

[ref-primary-key]: /reference/data-modeling/dimensions#primary_key