Reference
Data modeling
Measures

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.

YAML
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    count: {
      sql: `id`,
      type: `count`,
    },
 
    total_amount: {
      sql: `amount`,
      type: `sum`,
    },
  },
});

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.

YAML
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    orders_count: {
      sql: `id`,
      type: `count`,
      description: `Count of all orders`,
    },
  },
});

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.

YAML
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    revenue: {
      type: `sum`,
      sql: `price`,
      drill_members: [id, price, status, products.name, products.id],
    },
  },
});

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
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    orders_completed_count: {
      sql: `id`,
      type: `count`,
      filters: [{ sql: `${CUBE}.status = 'completed'` }],
    },
  },
});

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.

YAML
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    total: {
      sql: `amount`,
      type: `sum`,
      format: `currency`,
    },
  },
});

meta

Custom metadata. Can be used to pass any information to the frontend.

YAML
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    revenue: {
      type: `sum`,
      sql: `price`,
      meta: {
        any: "value",
      },
    },
  },
});

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

YAML
JavaScript
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
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    cumulative_count: {
      type: `count`,
      rolling_window: {
        trailing: `unbounded`,
      },
    },
  },
});

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
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    orders_count: {
      sql: `id`,
      type: `count`,
      public: false,
    },
  },
});

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 for detailed information on the corresponding sql parameter.

YAML
JavaScript
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).

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
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    orders_count: {
      title: `Number of Orders Placed`,
      sql: `id`,
      type: `count`,
    },
  },
});

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.

YAML
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    orders_count: {
      sql: `id`,
      type: `count`,
    },
  },
});

Calculated measures

In the case where you need to specify a formula for measure calculating with other measures, you can compose a formula in sql. For example, to calculate the conversion of buyers of all users.

YAML
JavaScript
cube(`orders`, {
  // ...
 
  measures: {
    purchases_to_created_account_ratio: {
      sql: `${purchases} / ${users.count} * 100.0`,
      type: `number`,
      format: `percent`,
    },
  },
});

You can create calculated measures from several joined cubes. In this case, a join will be created automatically.