Implementing custom time dimension granularities

This recipe shows examples of commonly used custom granularities.

Use case

Sometimes, you might need to group the result set by units of time that are different from default granularities such as week (starting on Monday) or year (starting on January 1).

Below, we explore the following examples of custom granularities:

Week starting on Sunday, commonly used in the US and some other countries.
Fiscal year (opens in a new tab) and fiscal quarter, commonly used in accounting and financial reporting.

Data modeling

Consider the following data model. interval and offset parameters are used to configure each custom granularity in granularities.

Note that custom granularities are also exposed via proxy dimensions so that we can conveniently query them via Playground or BI tools connected via the SQL API. We can also use them in further calculations like rendering fiscal_quarter_label.

YAML

JavaScript

cubes:  - name: custom_granularities  sql: |  SELECT '2024-01-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-02-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-03-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-04-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-05-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-06-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-07-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-08-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-09-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-10-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-11-15'::TIMESTAMP AS timestamp UNION ALL  SELECT '2024-12-15'::TIMESTAMP AS timestamp    dimensions:  - name: timestamp  sql: timestamp  type: time    granularities:  - name: sunday_week  interval: 1 week  offset: -1 day    - name: fiscal_year  title: Federal fiscal year in the United States  interval: 1 year  offset: -3 months    - name: fiscal_quarter  title: Federal fiscal quarter in the United States  interval: 1 quarter  offset: -3 months    - name: sunday_week  sql: "{timestamp.sunday_week}"  type: time    - name: fiscal_year  sql: "{timestamp.fiscal_year}"  type: time    - name: fiscal_quarter  sql: "{timestamp.fiscal_quarter}"  type: time    - name: fiscal_quarter_label  sql: |  'FY' || (EXTRACT(YEAR FROM {timestamp.fiscal_year}) + 1) ||  '-Q' || EXTRACT(QUARTER FROM {timestamp.fiscal_quarter} + INTERVAL '3 MONTHS')  type: string

Result

Querying this data modal would yield the following result:

String time dimensions Custom calendars