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 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

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

javascript

cube(`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: {
    timestamp: {
      sql: `timestamp`,
      type: `time`,

      granularities: {
        sunday_week: {
          interval: `1 week`,
          offset: `-1 day`
        },

        fiscal_year: {
          title: `Federal fiscal year in the United States`,
          interval: `1 year`,
          offset: `-3 months`
        },

        fiscal_quarter: {
          title: `Federal fiscal quarter in the United States`,
          interval: `1 quarter`,
          offset: `-3 months`
        }
      }
    },
    
    sunday_week: {
      sql: `${timestamp.sunday_week}`,
      type: `time`
    },

    fiscal_year: {
      sql: `${timestamp.fiscal_year}`,
      type: `time`
    },
      
    fiscal_quarter: {
      sql: `${timestamp.fiscal_quarter}`,
      type: `time`
    },

    fiscal_quarter_label: {
      sql: `
          'FY' || (EXTRACT(YEAR FROM {timestamp.fiscal_year}) + 1) ||
          '-Q' || EXTRACT(QUARTER FROM {timestamp.fiscal_quarter} + INTERVAL '3 MONTHS')
      `,
      type: `string`
    }
  }
})

</CodeTabs>

Result

Querying this data modal would yield the following result: