> ## Documentation Index > Fetch the complete documentation index at: https://cubed3-feat-druid-driver-streaming.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Measures > Measures are aggregations over columns in your database table, supporting types like count, sum, avg, rolling windows, and more. You can use the `measures` parameter within [cubes][ref-ref-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`](#name), [`sql`](#sql), and [`type`](#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][ref-naming]. ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: count sql: id type: count - name: total_amount sql: amount type: sum ``` ```javascript title="JavaScript" theme={null} 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. ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: orders_count title: Number of Orders Placed sql: id type: count ``` ```javascript title="JavaScript" theme={null} 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][ref-playground] and exposed to data consumers via [APIs and integrations][ref-apis]. ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: orders_count description: Count of all orders sql: id type: count ``` ```javascript title="JavaScript" theme={null} 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`. ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: orders_count sql: id type: count public: false ``` ```javascript title="JavaScript" theme={null} 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][ref-ai-context] without exposing it in the user interface. ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: revenue type: sum sql: price meta: any: value ai_context: > Use this measure for all revenue-related questions. ``` ```javascript title="JavaScript" theme={null} 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. ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: users_count sql: "COUNT(*)" type: number ``` ```javascript title="JavaScript" theme={null} cube(`orders`, { // ... measures: { users_count: { sql: `COUNT(*)`, type: `number` } } }) ``` Depending on the measure [type](#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][ref-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`](#sql) parameter for `number` type measures), because the mask replaces the entire measure expression including aggregation: ```yaml title="YAML" theme={null} 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)" ``` ```javascript title="JavaScript" theme={null} 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][ref-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: ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: orders_completed_count sql: id type: count filters: - sql: "{CUBE}.status = 'completed'" ``` ```javascript title="JavaScript" theme={null} 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][ref-schema-ref-preaggs-rollup]. | | `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][ref-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][link-tesseract]. Tesseract is currently in preview. Use the [`CUBEJS_TESSERACT_SQL_PLANNER`](/reference/configuration/environment-variables#cubejs_tesseract_sql_planner) environment variable to enable it. ```yaml title="YAML" theme={null} 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 ``` ```javascript title="JavaScript" theme={null} 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][ref-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][link-tesseract], rolling window calculations don't require the date range for the time dimension. Tesseract is currently in preview. Use the [`CUBEJS_TESSERACT_SQL_PLANNER`](/reference/configuration/environment-variables#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. ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: rolling_count_month sql: id type: count rolling_window: trailing: 1 month ``` ```javascript title="JavaScript" theme={null} 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: ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: cumulative_count type: count rolling_window: trailing: unbounded ``` ```javascript title="JavaScript" theme={null} 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][ref-multi-stage], e.g., [time-shift measures][ref-time-shift]. ```yaml title="YAML" theme={null} 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 ``` ```javascript title="JavaScript" theme={null} 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][ref-multi-stage] 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. ```yaml title="YAML" theme={null} measures: - name: country_revenue multi_stage: true sql: "{revenue}" type: sum grain: keep_only: - country ``` ```javascript title="JavaScript" theme={null} 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][ref-multi-stage]. They are still supported, but [`grain`](#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`](#grain) | Only the listed dimensions, ignoring query dimensions | | `reduce_by` | [`grain.exclude`](#grain) | Query dimensions minus the listed dimensions | | `add_group_by` | [`grain.include`](#grain) | Query dimensions plus the listed dimensions | Each accepts a list of dimension names from the same cube. For new data models, use [`grain`](#grain) instead. ### `filter` The `filter` parameter is used with [multi-stage measures][ref-multi-stage] 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][ref-tesseract-env] (`CUBEJS_TESSERACT_SQL_PLANNER=true`). ```yaml title="YAML" theme={null} measures: - name: amount_all_statuses multi_stage: true sql: "{total_amount}" type: number filter: exclude: - status ``` ```javascript title="JavaScript" theme={null} 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][ref-filters-query], and `and` / `or` groups can be nested arbitrarily: ```yaml title="YAML" theme={null} 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] ``` ```javascript title="JavaScript" theme={null} 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][ref-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`. ```yaml title="YAML" theme={null} 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 ``` ```javascript title="JavaScript" theme={null} 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. ```yaml title="YAML" theme={null} 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 ``` ```javascript title="JavaScript" theme={null} 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][ref-calendar-cubes]. 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. ```yaml title="YAML" theme={null} 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 ``` ```javascript title="JavaScript" theme={null} 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][ref-switch-dimensions]. `case` measures are powered by Tesseract, the [next-generation data modeling engine][link-tesseract]. Tesseract is currently in preview. Use the [`CUBEJS_TESSERACT_SQL_PLANNER`](/reference/configuration/environment-variables#cubejs_tesseract_sql_planner) environment variable to enable it. You do not need to include the [`sql` parameter](#sql) if the `case` parameter is used. However, the [`multi_stage` parameter](#multi_stage) must be set to `true` for `case` measures. ```yaml title="YAML" theme={null} 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 ``` ```javascript title="JavaScript" theme={null} 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][link-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][link-d3-format] specifier string directly (e.g., `$,.2f`, `.0%`, `.2s`). ```yaml title="YAML" theme={null} 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" ``` ```javascript title="JavaScript" theme={null} 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][link-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. ```yaml title="YAML" theme={null} 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 ``` ```javascript title="JavaScript" theme={null} 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][ref-drilldowns] 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][ref-drilldowns]. ```yaml title="YAML" theme={null} cubes: - name: orders # ... measures: - name: revenue type: sum sql: price drill_members: - id - price - status - products.name - products.id ``` ```javascript title="JavaScript" theme={null} cube(`orders`, { // ... measures: { revenue: { type: `sum`, sql: `price`, drill_members: [id, price, status, products.name, products.id] } } }) ``` [ref-ai-context]: /docs/data-modeling/ai-context [ref-ref-cubes]: /reference/data-modeling/cube [ref-schema-ref-types-formats-measures-formats]: /reference/data-modeling/measures#format [ref-drilldowns]: /reference/recipes/drilldowns [ref-naming]: /docs/data-modeling/syntax#naming [ref-playground]: /docs/explore-analyze/playground [ref-apis]: /reference [ref-rolling-window]: /docs/data-modeling/measures#rolling-windows [link-tesseract]: https://cube.dev/blog/introducing-next-generation-data-modeling-engine [ref-multi-stage]: /docs/data-modeling/measures#multi-stage-measures [ref-time-shift]: /docs/data-modeling/measures#time-shift [ref-nested-aggregate]: /docs/data-modeling/measures#nested-aggregates [ref-calendar-cubes]: /docs/data-modeling/concepts/calendar-cubes [ref-switch-dimensions]: /reference/data-modeling/dimensions#type [ref-tesseract-env]: /reference/configuration/environment-variables#cubejs_tesseract_sql_planner [ref-filters-query]: /reference/core-data-apis/rest-api/query-format#filters-format [ref-data-masking]: /docs/data-modeling/data-access-policies#data-masking [link-d3-format]: https://d3js.org/d3-format [link-iso-4217]: https://en.wikipedia.org/wiki/ISO_4217 [ref-calculated-measures]: /docs/data-modeling/measures#calculated-measures [ref-schema-ref-preaggs-rollup]: /reference/data-modeling/pre-aggregations#rollup