Measures

You can use the measures parameter within cubes to define measures. Each measure is an aggregation over a certain column in your database table. Any measure should have the following parameters: name, sql, and type.

Parameters

`name`

The name parameter serves as the identifier of a measure. It must be unique among all measures, dimensions, and segments within a cube and follow the naming conventions.

cubes:
  - name: orders
    # ...

    measures:
      - name: count
        sql: id
        type: count

      - name: total_amount
        sql: amount
        type: sum

cube(`orders`, {
  // ...

  measures: {
    count: {
      sql: `id`,
      type: `count`
    },

    total_amount: {
      sql: `amount`,
      type: `sum`
    }
  }
})

`title`

You can use the title parameter to change a measure’s displayed name. By default, Cube will humanize your measure key to create a display name. In order to override default behavior, please use the title parameter.

cubes:
  - name: orders
    # ...

    measures:
      - name: orders_count
        title: Number of Orders Placed
        sql: id
        type: count

cube(`orders`, {
  // ...

  measures: {
    orders_count: {
      title: `Number of Orders Placed`,
      sql: `id`,
      type: `count`
    }
  }
})

`description`

This parameter provides a human-readable description of a measure. When applicable, it will be displayed in Playground and exposed to data consumers via APIs and integrations.

cubes:
  - name: orders
    # ...

    measures:
      - name: orders_count
        description: Count of all orders
        sql: id
        type: count

cube(`orders`, {
  // ...

  measures: {
    orders_count: {
      sql: `id`,
      type: `count`,
      description: `Count of all orders`
    }
  }
})

`public`

The public parameter is used to manage the visibility of a measure. Valid values for public are true and false. When set to false, this measure cannot be queried through the API. Defaults to true.

cubes:
  - name: orders
    # ...

    measures:
      - name: orders_count
        sql: id
        type: count
        public: false

cube(`orders`, {
  // ...

  measures: {
    orders_count: {
      sql: `id`,
      type: `count`,
      public: false
    }
  }
})

`meta`

Custom metadata. Can be used to pass any information to the frontend. You can also use the ai_context key to provide context to the AI agent without exposing it in the user interface.

cubes:
  - name: orders
    # ...

    measures:
      - name: revenue
        type: sum
        sql: price
        meta:
          any: value
          ai_context: >
            Use this measure for all revenue-related questions.

cube(`orders`, {
  // ...

  measures: {
    revenue: {
      type: `sum`,
      sql: `price`,
      meta: {
        any: "value",
        ai_context: `Use this measure for all revenue-related questions.`
      }
    }
  }
})

`sql`

sql is a required parameter. It can take any valid SQL expression depending on the type of the measure. Please refer to the [Measure Types Guide][ref-schema-ref-types-formats-measures-types] for detailed information on the corresponding sql parameter.

cubes:
  - name: orders
    # ...

    measures:
      - name: users_count
        sql: "COUNT(*)"
        type: number

cube(`orders`, {
  // ...

  measures: {
    users_count: {
      sql: `COUNT(*)`,
      type: `number`
    }
  }
})

Depending on the measure type, the sql parameter would either:

Be skipped (in case of the count type).
Contain an aggregate function, e.g., STRING_AGG(string_dimension, ',') (in case of string, time, boolean, and number types).
Contain a non-aggregated expression that Cube would wrap into an aggregate function according to the measure type (in case of the avg, count_distinct, count_distinct_approx, min, max, and sum types).

`mask`

The optional mask parameter defines the replacement value used when the measure is masked by a data masking access policy. The mask can be a static value (number, boolean, or string) or a SQL expression. When using a SQL expression, it should be an aggregate expression (the same way as the measure’s sql parameter for number type measures), because the mask replaces the entire measure expression including aggregation:

cubes:
  - name: orders
    # ...

    measures:
      - name: count
        type: count
        mask: 0

      - name: total_revenue
        sql: revenue
        type: sum
        mask: -1

      - name: avg_revenue
        sql: revenue
        type: avg
        mask:
          sql: "AVG(CASE WHEN {CUBE}.is_public THEN {CUBE}.revenue END)"

cube(`orders`, {
  // ...

  measures: {
    count: {
      type: `count`,
      mask: 0
    },

    total_revenue: {
      sql: `revenue`,
      type: `sum`,
      mask: -1
    },

    avg_revenue: {
      sql: `revenue`,
      type: `avg`,
      mask: {
        sql: `AVG(CASE WHEN ${CUBE}.is_public THEN ${CUBE}.revenue END)`
      }
    }
  }
})

If no mask is defined, the default mask value is NULL. See data masking for more details.

SQL masks on measures are not applied in ungrouped queries (e.g., SELECT * via the SQL API). If you need dynamic masking in ungrouped mode, use a masked dimension instead.

`filters`

If you want to add some conditions for a metric’s calculation, you should use the filters parameter. The syntax looks like the following:

cubes:
  - name: orders
    # ...

    measures:
      - name: orders_completed_count
        sql: id
        type: count
        filters:
          - sql: "{CUBE}.status = 'completed'"

cube(`orders`, {
  // ...

  measures: {
    orders_completed_count: {
      sql: `id`,
      type: `count`,
      filters: [{ sql: `${CUBE}.status = 'completed'` }]
    }
  }
})

`type`

type is a required parameter. There are various types that can be assigned to a measure. A measure can only have one type.

Type	`sql` parameter	Description
`count`	Optional	Table count, similar to SQL `COUNT`. Properly handles join row multiplication.
`count_distinct`	Non-aggregated expression	Number of distinct values, similar to SQL `COUNT(DISTINCT …)`.
`count_distinct_approx`	Non-aggregated expression	Approximate distinct count using HyperLogLog. Unlike `count_distinct`, it is additive and can be used in rollup pre-aggregations.
`sum`	Non-aggregated numeric expression	Sum of values, similar to SQL `SUM`. Properly handles join row duplication.
`avg`	Non-aggregated numeric expression	Average of values, similar to SQL `AVG`. Properly handles join row duplication.
`min`	Non-aggregated numeric expression	Minimum value, similar to SQL `MIN`.
`max`	Non-aggregated numeric expression	Maximum value, similar to SQL `MAX`.
`number`	Aggregate expression	Arithmetic on other measures, e.g., in calculated measures.
`number_agg`	Custom aggregate function	Custom aggregate not covered by other types (e.g., `PERCENTILE_CONT`). Tesseract only.
`string`	Aggregate expression returning string	String-valued measure.
`time`	Aggregate expression returning timestamp	Timestamp-valued measure.
`boolean`	Aggregate expression returning boolean	Boolean-valued measure.

The number_agg type is only available in Tesseract, the next-generation data modeling engine. Tesseract is currently in preview. Use the CUBEJS_TESSERACT_SQL_PLANNER environment variable to enable it.

cubes:
  - name: orders
    # ...

    measures:
      - name: orders_count
        type: count

      - name: unique_user_count
        sql: user_id
        type: count_distinct

      - name: revenue
        sql: amount
        type: sum

      - name: avg_transaction
        sql: amount
        type: avg

      - name: purchases_ratio
        sql: "1.0 * {purchases} / {orders_count}"
        type: number
        format: percent

cube(`orders`, {
  // ...

  measures: {
    orders_count: {
      type: `count`
    },

    unique_user_count: {
      sql: `user_id`,
      type: `count_distinct`
    },

    revenue: {
      sql: `amount`,
      type: `sum`
    },

    avg_transaction: {
      sql: `amount`,
      type: `avg`
    },

    purchases_ratio: {
      sql: `1.0 * ${purchases} / ${orders_count}`,
      type: `number`,
      format: `percent`
    }
  }
})

`rolling_window`

The rolling_window parameter is used to for rolling window calculations, e.g., to calculate a metric over a moving window of time, e.g. a week or a month.

Rolling window calculations require the query to contain a single time dimension with a provided date range. It is used to calculate the minimum and maximum values for the series of time windows.With Tesseract, the next-generation data modeling engine, rolling window calculations don’t require the date range for the time dimension. Tesseract is currently in preview. Use the CUBEJS_TESSERACT_SQL_PLANNER environment variable to enable it.

`offset`

The offset parameter is used to specify the starting point of the time window. You can set the window offset parameter to either start or end, which will match the start or end of the window. By default, the offset parameter is set to end.

`trailing` and `leading`

The trailing and leading parameters define the size of the time window. The trailing parameter defines the size of the window part before the offset point, and the leading parameter defines the size of the window part after the offset point. These parameters have a format defined as (-?\d+) (minute|hour|day|week|month|year). It means that you can define these parameters using both positive and negative integers. The trailing and leading parameters can also be set to unbounded, which means infinite size for the corresponding window part. By default, the leading and trailing parameters are set to zero.

cubes:
  - name: orders
    # ...

    measures:
      - name: rolling_count_month
        sql: id
        type: count
        rolling_window:
          trailing: 1 month

cube(`orders`, {
  // ...

  measures: {
    rolling_count_month: {
      sql: `id`,
      type: `count`,
      rolling_window: {
        trailing: `1 month`
      }
    }
  }
})

Here’s an example of an unbounded window that’s used for cumulative counts:

cubes:
  - name: orders
    # ...

    measures:
      - name: cumulative_count
        type: count
        rolling_window:
          trailing: unbounded

cube(`orders`, {
  // ...

  measures: {
    cumulative_count: {
      type: `count`,
      rolling_window: {
        trailing: `unbounded`
      }
    }
  }
})

`multi_stage`

The multi_stage parameter is used to define measures that are used with multi-stage calculations, e.g., time-shift measures.

cubes:
  - name: time_shift
    sql: >
      SELECT '2024-01-01'::TIMESTAMP AS time, 100 AS revenue UNION ALL
      SELECT '2024-02-01'::TIMESTAMP AS time, 200 AS revenue UNION ALL
      SELECT '2024-03-01'::TIMESTAMP AS time, 300 AS revenue UNION ALL

      SELECT '2025-01-01'::TIMESTAMP AS time, 400 AS revenue UNION ALL
      SELECT '2025-02-01'::TIMESTAMP AS time, 500 AS revenue UNION ALL
      SELECT '2025-03-01'::TIMESTAMP AS time, 600 AS revenue

    dimensions:
      - name: time
        sql: time
        type: time

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

      - name: revenue_prior_year
        multi_stage: true
        sql: "{revenue}"
        type: number
        time_shift:
          - time_dimension: time
            interval: 1 year
            type: prior

cube(`time_shift`, {
  sql: `
    SELECT '2024-01-01'::TIMESTAMP AS time, 100 AS revenue UNION ALL
    SELECT '2024-02-01'::TIMESTAMP AS time, 200 AS revenue UNION ALL
    SELECT '2024-03-01'::TIMESTAMP AS time, 300 AS revenue UNION ALL

    SELECT '2025-01-01'::TIMESTAMP AS time, 400 AS revenue UNION ALL
    SELECT '2025-02-01'::TIMESTAMP AS time, 500 AS revenue UNION ALL
    SELECT '2025-03-01'::TIMESTAMP AS time, 600 AS revenue
  `,

  dimensions: {
    time: {
      sql: `time`,
      type: `time`
    }
  },

  measures: {
    revenue: {
      sql: `revenue`,
      type: `sum`
    },

    revenue_prior_year: {
      multi_stage: true,
      sql: `${revenue}`,
      type: `number`,
      time_shift: [
        {
          time_dimension: `time`,
          interval: `1 year`,
          type: `prior`
        }
      ]
    }
  }
})

`grain`

The grain parameter is used with multi-stage measures to control the dimensions of the inner aggregation stage’s GROUP BY — the grain at which the base measure is computed before the outer aggregation is applied. It accepts an object with three keys, each taking a list of dimension names from the same cube:

keep_only — group the inner stage by only the listed dimensions, ignoring the query’s dimensions. Use it for fixed-grain calculations such as percent of total.
exclude — group the inner stage by the query’s dimensions minus the listed dimensions. Use it for ranking within groups.
include — group the inner stage by the query’s dimensions plus the listed dimensions. Use it for nested aggregates (an aggregate of an aggregate).

keep_only and exclude are mutually exclusive.

measures:
  - name: country_revenue
    multi_stage: true
    sql: "{revenue}"
    type: sum
    grain:
      keep_only:
        - country

measures: {
  country_revenue: {
    multi_stage: true,
    sql: `${revenue}`,
    type: `sum`,
    grain: {
      keep_only: [country]
    }
  }
}

`grain` key	Inner `GROUP BY`	Outer `GROUP BY`
`keep_only`	Only the listed dimensions	Query dimensions
`exclude`	Query dimensions minus listed	Query dimensions
`include`	Query dimensions plus listed	Query dimensions

grain replaces the standalone group_by, reduce_by, and add_group_by parameters, which remain supported. To migrate, use grain.keep_only instead of group_by, grain.exclude instead of reduce_by, and grain.include instead of add_group_by.

`group_by`, `reduce_by`, and `add_group_by` (legacy)

These three parameters were the original way to control the inner aggregation stage’s GROUP BY for multi-stage measures. They are still supported, but grain now covers all three and is the recommended way to express the grain of a multi-stage measure.

Legacy parameter	`grain` equivalent	Effect on the inner stage’s `GROUP BY`
`group_by`	`grain.keep_only`	Only the listed dimensions, ignoring query dimensions
`reduce_by`	`grain.exclude`	Query dimensions minus the listed dimensions
`add_group_by`	`grain.include`	Query dimensions plus the listed dimensions

Each accepts a list of dimension names from the same cube. For new data models, use grain instead.

`filter`

The filter parameter is used with multi-stage measures to override the filter context that the inner aggregation stage inherits from the query. By default, a multi-stage measure inherits all filters applied to the query; filter lets you drop, replace, or extend those filters at the inner stage. This is commonly used for “share of total” calculations where the denominator must ignore a filter applied by the query — for example, computing each status’s share of the per-category total while the query is filtered to a single status.

The filter parameter requires the Tesseract SQL planner (CUBEJS_TESSERACT_SQL_PLANNER=true).

measures:
  - name: amount_all_statuses
    multi_stage: true
    sql: "{total_amount}"
    type: number
    filter:
      exclude:
        - status

measures: {
  amount_all_statuses: {
    multi_stage: true,
    sql: `${total_amount}`,
    type: `number`,
    filter: {
      exclude: [status]
    }
  }
}

filter accepts the following keys, which can be combined:

Key	Description
`exclude`	A list of members (dimensions or segments) whose query filters are dropped at the inner stage.
`keep_only`	A list of members whose query filters are kept; all other inherited filters are dropped.
`include`	A list of additional predicates injected at the inner stage, regardless of the query. Each entry is a query-style filter (`member`, `operator`, `values`) and entries may be nested into `and` / `or` groups.
`mode`	How the override composes along a multi-stage chain: `relative` (default) or `fixed`.

The mode key controls how the override interacts with the filter context of an upstream multi-stage measure when measures are chained:

relative (default): the override is applied relative to the filter context inherited from the chain.
fixed: the override is applied as an absolute filter context, ignoring the filters inherited from upstream multi-stage measures.

At the top level (not inside a chain), relative and fixed are equivalent. An include entry uses the same shape as a query filter, and and / or groups can be nested arbitrarily:

measures:
  - name: high_value_west_coast
    multi_stage: true
    sql: "{total_amount}"
    type: number
    filter:
      mode: relative
      exclude:
        - status
      include:
        - member: amount
          operator: gt
          values: ["0"]
        - or:
            - member: city
              operator: equals
              values: [NYC]
            - member: city
              operator: equals
              values: [SF]

measures: {
  high_value_west_coast: {
    multi_stage: true,
    sql: `${total_amount}`,
    type: `number`,
    filter: {
      mode: `relative`,
      exclude: [status],
      include: [
        { member: amount, operator: `gt`, values: [`0`] },
        { or: [
          { member: city, operator: `equals`, values: [`NYC`] },
          { member: city, operator: `equals`, values: [`SF`] }
        ] }
      ]
    }
  }
}

`time_shift`

The time_shift parameter is used to configure a time shift for a measure. It accepts an array of time shift configurations that consist of time_dimension, type, interval, and name parameters.

`type` and `interval`

These parameters define the time shift direction and size. The type can be either prior (shifting time backwards) or next (shifting time forwards). The interval parameter defines the size of the time shift and has the following format: quantity unit, e.g., 1 year or 7 days.

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

      - name: revenue_7d_ago
        multi_stage: true
        sql: "{revenue}"
        type: number
        time_shift:
          - interval: 7 days
            type: prior

      - name: revenue_1y_ago
        multi_stage: true
        sql: "{revenue}"
        type: number
        time_shift:
          - interval: 1 year
            type: prior

  measures: {
    revenue: {
      sql: `revenue`,
      type: `sum`
    },

    revenue_7d_ago: {
      multi_stage: true,
      sql: `${revenue}`,
      type: `number`,
      time_shift: [
        {
          interval: `7 days`,
          type: `prior`
        }
      ]
    },

    revenue_1y_ago: {
      multi_stage: true,
      sql: `${revenue}`,
      type: `number`,
      time_shift: [
        {
          interval: `1 year`,
          type: `prior`
        }
      ]
    }
  }

`time_dimension`

The time_dimension parameter is used to specify the time dimension for the time shift. If it’s omitted, Cube will apply the time shift to all time dimensions in the query. In this case, only single time shift configuration is allowed in time_shift. If time_dimension is specified, the time shift will only happen if the query contains this very time dimension. This is useful if you’d like to apply different time shifts to different time dimensions or if you want to apply a time shift only when a specific time dimension is present in the query.

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

      - name: lagging_revenue
        multi_stage: true
        sql: "{revenue}"
        type: number
        time_shift:
          - time_dimension: purchase_date
            interval: 3 months
            type: prior

          - time_dimension: shipping_date
            interval: 2 months
            type: prior

          - time_dimension: delivery_date
            interval: 1 month
            type: prior

  measures: {
    revenue: {
      sql: `revenue`,
      type: `sum`
    },

    lagging_revenue: {
      multi_stage: true,
      sql: `${revenue}`,
      type: `number`,
      time_shift: [
        {
          time_dimension: `purchase_date`,
          interval: `3 months`,
          type: `prior`
        },
        {
          time_dimension: `shipping_date`,
          interval: `2 months`,
          type: `prior`
        },
        {
          time_dimension: `delivery_date`,
          interval: `1 month`,
          type: `prior`
        }
      ]
    }
  }

`name`

The name parameter is used to reference a named time shift that is defined on a time dimension from a calendar cube. Named time shifts are used in cases when different measures use the same time shift configuration (e.g., prior + 1 year) but have to be shifted differently depending on the custom calendar.

cubes:
  - name: sales_calendar
    calendar: true
    sql: >
      SELECT '2025-06-02Z' AS date, '2024-06-01Z' AS mapped_date, '2024-06-03Z' AS mapped_date_alt UNION ALL
      SELECT '2025-06-03Z' AS date, '2024-06-02Z' AS mapped_date, '2024-06-04Z' AS mapped_date_alt UNION ALL
      SELECT '2025-06-04Z' AS date, '2024-06-03Z' AS mapped_date, '2024-06-05Z' AS mapped_date_alt UNION ALL
      SELECT '2025-06-05Z' AS date, '2024-06-04Z' AS mapped_date, '2024-06-06Z' AS mapped_date_alt UNION ALL
      SELECT '2025-06-06Z' AS date, '2024-06-05Z' AS mapped_date, '2024-06-07Z' AS mapped_date_alt UNION ALL
      SELECT '2025-06-07Z' AS date, '2024-06-06Z' AS mapped_date, '2024-06-08Z' AS mapped_date_alt UNION ALL
      SELECT '2025-06-08Z' AS date, '2024-06-07Z' AS mapped_date, '2024-06-09Z' AS mapped_date_alt

    dimensions:
      - name: date_key
        sql: "{CUBE}.date::TIMESTAMP"
        type: time
        primary_key: true

      - name: date
        sql: "{CUBE}.date::TIMESTAMP"
        type: time
        time_shift:
          - name: 1_year_prior
            sql: "{CUBE}.mapped_date::TIMESTAMP"

          - name: 1_year_prior_alternative
            sql: "{CUBE}.mapped_date_alt::TIMESTAMP"

  - name: sales
    sql: >
      SELECT 101 AS id, '2024-06-01Z' AS date, 101 AS amount UNION ALL
      SELECT 102 AS id, '2024-06-02Z' AS date, 102 AS amount UNION ALL
      SELECT 103 AS id, '2024-06-03Z' AS date, 103 AS amount UNION ALL
      SELECT 104 AS id, '2024-06-04Z' AS date, 104 AS amount UNION ALL
      SELECT 105 AS id, '2024-06-05Z' AS date, 105 AS amount UNION ALL
      SELECT 106 AS id, '2024-06-06Z' AS date, 106 AS amount UNION ALL
      SELECT 107 AS id, '2024-06-07Z' AS date, 107 AS amount UNION ALL
      SELECT 108 AS id, '2024-06-08Z' AS date, 108 AS amount UNION ALL
      SELECT 109 AS id, '2024-06-09Z' AS date, 109 AS amount UNION ALL

      SELECT 202 AS id, '2025-06-02Z' AS date, 202 AS amount UNION ALL
      SELECT 203 AS id, '2025-06-03Z' AS date, 203 AS amount UNION ALL
      SELECT 204 AS id, '2025-06-04Z' AS date, 204 AS amount UNION ALL
      SELECT 205 AS id, '2025-06-05Z' AS date, 205 AS amount UNION ALL
      SELECT 206 AS id, '2025-06-06Z' AS date, 206 AS amount UNION ALL
      SELECT 207 AS id, '2025-06-07Z' AS date, 207 AS amount UNION ALL
      SELECT 208 AS id, '2025-06-08Z' AS date, 208 AS amount

    joins:
      - name: sales_calendar
        sql: "{sales.date} = {sales_calendar.date_key}"
        relationship: many_to_one

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

      - name: date
        sql: "{CUBE}.date::TIMESTAMP"
        type: time
        public: false

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

      - name: total_amount_1y_prior
        multi_stage: true
        sql: "{total_amount}"
        type: number
        time_shift:
          - name: 1_year_prior

      - name: total_amount_1y_prior_alternative
        multi_stage: true
        sql: "{total_amount}"
        type: number
        time_shift:
          - name: 1_year_prior_alternative

cube(`sales_calendar`, {
  sql: `
    SELECT '2025-06-02Z' AS date, '2024-06-01Z' AS mapped_date, '2024-06-03Z' AS mapped_date_alt UNION ALL
    SELECT '2025-06-03Z' AS date, '2024-06-02Z' AS mapped_date, '2024-06-04Z' AS mapped_date_alt UNION ALL
    SELECT '2025-06-04Z' AS date, '2024-06-03Z' AS mapped_date, '2024-06-05Z' AS mapped_date_alt UNION ALL
    SELECT '2025-06-05Z' AS date, '2024-06-04Z' AS mapped_date, '2024-06-06Z' AS mapped_date_alt UNION ALL
    SELECT '2025-06-06Z' AS date, '2024-06-05Z' AS mapped_date, '2024-06-07Z' AS mapped_date_alt UNION ALL
    SELECT '2025-06-07Z' AS date, '2024-06-06Z' AS mapped_date, '2024-06-08Z' AS mapped_date_alt UNION ALL
    SELECT '2025-06-08Z' AS date, '2024-06-07Z' AS mapped_date, '2024-06-09Z' AS mapped_date_alt
  `,

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

    date: {
      sql: `${CUBE}.date::TIMESTAMP`,
      type: `time`,
      time_shift: [
        {
          name: `1_year_prior`,
          sql: `${CUBE}.mapped_date::TIMESTAMP`
        },
        {
          name: `1_year_prior_alternative`,
          sql: `${CUBE}.mapped_date_alt::TIMESTAMP`
        }
      ]
    }
  }
})

cube(`sales`, {
  sql: `
    SELECT 101 AS id, '2024-06-01Z' AS date, 101 AS amount UNION ALL
    SELECT 102 AS id, '2024-06-02Z' AS date, 102 AS amount UNION ALL
    SELECT 103 AS id, '2024-06-03Z' AS date, 103 AS amount UNION ALL
    SELECT 104 AS id, '2024-06-04Z' AS date, 104 AS amount UNION ALL
    SELECT 105 AS id, '2024-06-05Z' AS date, 105 AS amount UNION ALL
    SELECT 106 AS id, '2024-06-06Z' AS date, 106 AS amount UNION ALL
    SELECT 107 AS id, '2024-06-07Z' AS date, 107 AS amount UNION ALL
    SELECT 108 AS id, '2024-06-08Z' AS date, 108 AS amount UNION ALL
    SELECT 109 AS id, '2024-06-09Z' AS date, 109 AS amount UNION ALL

    SELECT 202 AS id, '2025-06-02Z' AS date, 202 AS amount UNION ALL
    SELECT 203 AS id, '2025-06-03Z' AS date, 203 AS amount UNION ALL
    SELECT 204 As id, '2025-06-04Z' As date, 204 As amount UNION ALL
    SELECT 205 As id, '2025-06-05Z' As date, 205 As amount UNION ALL
    SELECT 206 As id, '2025-06-06Z' As date, 206 As amount UNION ALL
    SELECT 207 As id, '2025-06-07Z' As date, 207 As amount UNION ALL
    SELECT 208 As id, '2025-06-08Z' As date, 208 As amount
  `,

  joins: {
    sales_calendar: {
      sql: `${sales}.date = ${sales_calendar}.date_key`,
      relationship: `many_to_one`
    }
  },

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

    date: {
      sql: `${CUBE}.date::TIMESTAMP`,
      type: `time`,
      public: false
    }
  },
  
  measures: {
    total_amount: {
      sql: `amount`,
      type: `sum`
    },

    total_amount_1y_prior: {
      multi_stage: true,
      sql: `${total_amount}`,
      type: `number`,
      time_shift: [{
        name: `1_year_prior`
      }]
    },

    total_amount_1y_prior_alternative: {
      multi_stage: true,
      sql: `${total_amount}`,
      type: `number`,
      time_shift: [{
        name: `1_year_prior_alternative`
      }]
    }
  }
)

Named time shifts also allow to reuse the same time shift configuration across multiple measures and cubes where they are defined.

`case`

The case parameter is used to define conditional measures, i.e., measures that are calculated based on the value of a switch dimension.

case measures are powered by Tesseract, the next-generation data modeling engine. Tesseract is currently in preview. Use the CUBEJS_TESSERACT_SQL_PLANNER environment variable to enable it.

You do not need to include the sql parameter if the case parameter is used. However, the multi_stage parameter must be set to true for case measures.

cubes:
  - name: orders
    # ...

    dimensions:
      - name: currency
        type: switch
        values:
          - USD
          - EUR
          - GBP
    
    measures:
      - name: amount_usd
        sql: amount_usd
        type: sum

      - name: amount_eur
        sql: amount_eur
        type: sum

      - name: amount_gbp
        sql: amount_gbp
        type: sum

      - name: amount_in_currency
        multi_stage: true
        case:
          switch: "{CUBE.currency}"
          when:
            - value: EUR
              sql: "{CUBE.amount_eur}"
            - value: GBP
              sql: "{CUBE.amount_gbp}"
          else:
            sql: "{CUBE.amount_usd}"
        type: number

cube(`orders`, {
  // ...

  dimensions: {
    currency: {
      type: `switch`,
      values: [
        `USD`,
        `EUR`,
        `GBP`
      ]
    }
  },

  measures: {
    amount_usd: {
      sql: `amount_usd`,
      type: `sum`
    },

    amount_eur: {
      sql: `amount_eur`,
      type: `sum`
    },

    amount_gbp: {
      sql: `amount_gbp`,
      type: `sum`
    },

    amount_in_currency: {
      multi_stage: true,
      case: {
        switch: `${CUBE.currency}`,
        when: [
          {
            value: `EUR`,
            sql: `${CUBE.amount_eur}`
          },
          {
            value: `GBP`,
            sql: `${CUBE.amount_gbp}`
          }
        ],
        else: {
          sql: `${CUBE.amount_usd}`
        }
      },
      type: `number`
    }
  }
})

`format`

format is an optional parameter. It controls how measure values are displayed to data consumers. You can use a named format or a custom d3-format specifier string. Named formats accept an optional _N suffix (0–6) to set the maximum decimal precision; insignificant trailing zeros are trimmed, so N is an upper bound rather than a fixed number of digits. Without a suffix, named formats default to a maximum of 2 decimal places.

Format	Description	Example	Output
`number` / `number_N`	Grouped fixed-point	`number`	1,234.57
`percent` / `percent_N`	Percentage	`percent_1`	12.5%
`currency` / `currency_N`	Currency with grouping	`currency_0`	$1,235
`abbr` / `abbr_N`	SI prefix (K, M, G, …)	`abbr`	1.2K
`accounting` / `accounting_N`	Negative values in parentheses	`accounting_2`	(1,234.57)

For full control, you can also pass any valid d3-format specifier string directly (e.g., $,.2f, .0%, .2s).

cubes:
  - name: orders
    # ...

    measures:
      - name: total_amount
        sql: amount
        type: sum
        format: currency_2

      - name: conversion_rate
        sql: "1.0 * {completed_count} / {count}"
        type: number
        format: percent_1

      - name: total_revenue_abbr
        sql: revenue
        type: sum
        format: abbr

      - name: custom_formatted
        sql: amount
        type: sum
        format: "$,.2f"

cube(`orders`, {
  // ...

  measures: {
    total_amount: {
      sql: `amount`,
      type: `sum`,
      format: `currency_2`
    },

    conversion_rate: {
      sql: `1.0 * ${completed_count} / ${count}`,
      type: `number`,
      format: `percent_1`
    },

    total_revenue_abbr: {
      sql: `revenue`,
      type: `sum`,
      format: `abbr`
    },

    custom_formatted: {
      sql: `amount`,
      type: `sum`,
      format: `$,.2f`
    }
  }
})

`currency`

The optional currency parameter specifies the ISO 4217 currency code for the measure. Use it alongside format: currency to indicate which currency the values represent, so downstream tools can display the appropriate symbol. The value is a 3-letter currency code (e.g., USD, EUR, GBP) and is case-insensitive.

cubes:
  - name: orders
    # ...

    measures:
      - name: total_amount
        sql: amount
        type: sum
        format: currency
        currency: USD

      - name: total_amount_eur
        sql: amount_eur
        type: sum
        format: currency
        currency: EUR

cube(`orders`, {
  // ...

  measures: {
    total_amount: {
      sql: `amount`,
      type: `sum`,
      format: `currency`,
      currency: `USD`
    },

    total_amount_eur: {
      sql: `amount_eur`,
      type: `sum`,
      format: `currency`,
      currency: `EUR`
    }
  }
})

The currency parameter can only be used with numeric measure types (sum, avg, min, max, number, count, count_distinct, count_distinct_approx). Using it with string, boolean, or time measures will result in a validation error.

`drill_members`

Using the drill_members parameter, you can define a set of drill down fields for the measure. drill_members is defined as an array of dimensions. Cube automatically injects dimensions’ names and other cubes’ names with dimensions in the context, so you can reference these variables in the drill_members array. Learn more about how to define and use drill downs.

cubes:
  - name: orders
    # ...

    measures:
      - name: revenue
        type: sum
        sql: price
        drill_members:
          - id
          - price
          - status
          - products.name
          - products.id

cube(`orders`, {
  // ...

  measures: {
    revenue: {
      type: `sum`,
      sql: `price`,
      drill_members: [id, price, status, products.name, products.id]
    }
  }
})

Data Modeling

Configuration

Core Data APIs

Embed APIs

Other APIs

JavaScript SDK

Parameters

`name`

`title`

`description`

`public`

`meta`

`sql`

`mask`

`filters`

`type`

`rolling_window`

`offset`

`trailing` and `leading`

`multi_stage`

`grain`

`group_by`, `reduce_by`, and `add_group_by` (legacy)

`filter`

`time_shift`

`type` and `interval`

`time_dimension`

`name`

`case`

`format`

`currency`

`drill_members`

​Parameters

​name

​title

​description

​public

​meta

​sql

​mask

​filters

​type

​rolling_window

​offset

​trailing and leading

​multi_stage

​grain

​group_by, reduce_by, and add_group_by (legacy)

​filter

​time_shift

​type and interval

​time_dimension

​name

​case

​format

​currency

​drill_members

Parameters

`name`

`title`

`description`

`public`

`meta`

`sql`

`mask`

`filters`

`type`

`rolling_window`

`offset`

`trailing` and `leading`

`multi_stage`

`grain`

`group_by`, `reduce_by`, and `add_group_by` (legacy)

`filter`

`time_shift`

`type` and `interval`

`time_dimension`

`name`

`case`

`format`

`currency`

`drill_members`