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: sumtitle
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: countdescription
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: countpublic
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: falsemeta
Custom metadata. Can be used to pass any information to the frontend.
cubes: - name: orders # ... measures: - name: revenue type: sum sql: price meta: any: valuesql
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 for detailed information on the corresponding sql parameter.
cubes: - name: orders # ... measures: - name: users_count sql: "COUNT(*)" type: numberDepending on the measure type, the sql parameter would either:
- Be skipped (in case of the
counttype). - Contain an aggregate function, e.g.,
STRING_AGG(string_dimension, ',')(in case ofstring,time,boolean, andnumbertypes). - 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, andsumtypes).
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'"type
type is a required parameter. There are various types that can be assigned to a measure. Please refer to the Measure Types for the full list of measure types.
cubes: - name: orders # ... measures: - name: orders_count sql: id type: countrolling_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 (opens in a new tab), 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 monthHere'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: unboundedmulti_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: priortime_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: priortime_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: priorname
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_alternativeNamed time shifts also allow to reuse the same time shift configuration across multiple measures and cubes where they are defined.
format
format is an optional parameter. It is used to format the output of measures in different ways, for example, as currency for revenue. Please refer to the Measure Formats for the full list of supported formats.
cubes: - name: orders # ... measures: - name: total sql: amount type: sum format: currencydrill_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