Implementing custom time dimension granularities

This recipe show 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}) ||
          '-Q' || EXTRACT(QUARTER FROM {timestamp.fiscal_quarter})
        type: string

Calculating period-over-period changes Implementing data snapshots