Skip to Content
DocumentationData modelingReferenceTypes and formats

Types and Formats

Measure Types

This section describes the various types that can be assigned to a measure. A measure can only have one type.

string

This measure type allows defining measures as a string value.

The sql parameter is required and must include any valid SQL expression with an aggregate function that returns a value of the string type.

In the example below, we create a string measure by converting a numerical value to a string:

cube(`orders`, {
  // ...
 
  measures: {
    high_or_low: {
      type: `string`,
      sql: `CASE WHEN ${CUBE.number} > 100 THEN 'high' ELSE 'low' END`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: high_or_low
        sql: "CASE WHEN {CUBE.number} > 100 THEN 'high' ELSE 'low' END"
        type: string

time

This measure type allows defining measures as a time value.

The sql parameter is required and must include any valid SQL expression with an aggregate function that returns a value of the timestamp type.

In the example below, we create a time measure from an existing dimension:

cube(`orders`, {
  // ...
 
  measures: {
    last_order: {
      sql: `MAX(created_at)`,
      type: `time`
    }
  },
 
  dimensions: {
    created_at: {
      sql: `created_at`,
      type: `time`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: last_order
        sql: "MAX(created_at)"
        type: time
 
    dimensions:
      - name: created_at
        sql: created_at
        type: time

boolean

The boolean measure type can be used to condense data into a single boolean value.

The sql parameter is required and must include any valid SQL expression with an aggregate function that returns a value of the boolean type.

The example below adds an is_completed measure which only returns true if all orders have the completed status:

cube(`orders`, {
  // ...
 
  measures: {
    is_completed: {
      sql: `BOOL_AND(status = 'completed')`,
      type: `boolean`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: is_completed
        sql: "BOOL_AND(status = 'completed')"
        type: boolean

number

The number type is usually used, when performing arithmetic operations on measures, e.g., in calculated measures.

The sql parameter is required and must include any valid SQL expression with an aggregate function that returns a value of the numeric type.

cube(`orders`, {
  // ...
 
  measures: {
    purchases_ratio: {
      sql: `1.0 * ${purchases} / ${count}`,
      type: `number`,
      format: `percent`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: purchases_ratio
        sql: "1.0 * {purchases} / {count}"
        type: number
        format: percent

You can put any sql into number measure as long as it’s an aggregate expression:

cube(`orders`, {
  // ...
 
  measures: {
    ratio: {
      sql: `SUM(${CUBE}.amount) / count(*)`,
      type: `number`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: ratio
        sql: "SUM({CUBE}.amount) / count(*)"
        type: number

number_agg

The number_agg type is used when you need to write a custom aggregate function in the sql parameter that isn’t covered by standard measure types like sum, avg, min, etc.

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.

Unlike the number type which is used for calculations on measures (e.g., SUM(revenue) / COUNT(*)), number_agg indicates that the sql parameter contains a direct SQL aggregate function.

The sql parameter is required and must include a custom aggregate function that returns a numeric value.

cube(`orders`, {
  // ...
 
  measures: {
    median_price: {
      sql: `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price)`,
      type: `number_agg`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: median_price
        sql: "PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price)"
        type: number_agg

count

Performs a table count, similar to SQL’s COUNT function. However, unlike writing raw SQL, Cube will properly calculate counts even if your query’s joins will produce row multiplication.

You do not need to include the sql parameter for the measure of this type.

drill_members parameter is commonly used with type count. It allows users to click on the measure in the UI and inspect individual records that make up a count. Learn more about Drill Downs.

cube(`orders`, {
  // ...
 
  measures: {
    number_of_users: {
      type: `count`,
      // optional
      drill_members: [id, name, email, company]
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: number_of_users
        type: count
        drill_members:
          - id
          - name
          - email
          - company

count_distinct

Calculates the number of distinct values in a given field. It makes use of SQL’s COUNT DISTINCT function.

The sql parameter is required and must include any valid SQL expression of any type (without an aggregate function).

cube(`orders`, {
  // ...
 
  measures: {
    unique_user_count: {
      sql: `user_id`,
      type: `count_distinct`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: unique_user_count
        sql: user_id
        type: count_distinct

count_distinct_approx

Calculates approximate number of distinct values in a given field. Unlike count_distinct measure type, count_distinct_approx is decomposable aggregate function or additive. This allows its usage in additive rollup pre-aggregations which are much more versatile than non-additive ones. It uses special SQL backend-dependent functions to estimate distinct counts, usually based on HyperLogLog or similar algorithms. Wherever possible Cube will use multi-stage HLL which significantly improves calculation of distinct counts at scale.

The sql parameter is required and must include any valid SQL expression of any type (without an aggregate function).

cube(`orders`, {
  // ...
 
  measures: {
    unique_user_count: {
      sql: `user_id`,
      type: `count_distinct_approx`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: unique_user_count
        sql: user_id
        type: count_distinct_approx

sum

Adds up the values in a given field. It is similar to SQL’s SUM function. However, unlike writing raw SQL, Cube will properly calculate sums even if your query’s joins will result in row duplication.

The sql parameter is required and must include any valid SQL expression of the numeric type (without an aggregate function).

cube(`orders`, {
  // ...
 
  measures: {
    revenue: {
      sql: `amount`,
      type: `sum`
    },
 
    revenue_2: {
      sql: `${charges_amount}`,
      type: `sum`
    },
 
    revenue_3: {
      sql: `fee * 0.1`,
      type: `sum`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: revenue
        sql: amount
        type: sum
 
      - name: revenue_2
        sql: "{charges_amount}"
        type: sum
 
      - name: revenue_3
        sql: fee * 0.1
        type: sum

avg

Averages the values in a given field. It is similar to SQL’s AVG function. However, unlike writing raw SQL, Cube will properly calculate averages even if your query’s joins will result in row duplication.

The sql parameter is required and must include any valid SQL expression of the numeric type (without an aggregate function).

cube(`orders`, {
  // ...
 
  measures: {
    avg_transaction: {
      sql: `${transaction_amount}`,
      type: `avg`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: avg_transaction
        sql: "{transaction_amount}"
        type: avg

min

Type of measure min is calculated as a minimum of values defined in sql.

The sql parameter is required and must include any valid SQL expression of the numeric type (without an aggregate function).

cube(`orders`, {
  // ...
 
  measures: {
    date_first_purchase: {
      sql: `date_purchase`,
      type: `min`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: date_first_purchase
        sql: date_purchase
        type: min

max

Type of measure max is calculated as a maximum of values defined in sql.

The sql parameter is required and must include any valid SQL expression of the numeric type (without an aggregate function).

cube(`orders`, {
  // ...
 
  measures: {
    date_last_purchase: {
      sql: `date_purchase`,
      type: `max`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: date_last_purchase
        sql: date_purchase
        type: max

Measure Formats

When creating a measure you can explicitly define the format you’d like to see as output.

percent

percent is used for formatting numbers with a percent symbol.

cube(`orders`, {
  // ...
 
  measures: {
    purchase_conversion: {
      sql: `1.0 * ${purchase} / ${checkout}`,
      type: `number`,
      format: `percent`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: purchase_conversion
        sql: "1.0 * {purchase} / {checkout}"
        type: number
        format: percent

When using the percent format, do not multiply by 100 in your SQL expression. Presentation tools like Explore, Workbooks, and Dashboards apply this multiplication implicitly when displaying percent-formatted values, e.g., a value of 0.5 will be displayed as 50%.

Use 1.0 * numerator / denominator to ensure decimal division, as integer division would truncate the result, e.g., 5 / 10 may return 0, not 0.5, depending on your data source and column types.

currency

currency is used for monetary values.

cube(`orders`, {
  // ...
 
  measures: {
    total_amount: {
      sql: `amount`,
      type: `sum`,
      format: `currency`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: total_amount
        sql: amount
        type: sum
        format: currency

Custom numeric formats

Measures with numeric types support custom formatting using d3-format  specifier strings.

Custom numeric formatting is subject to support in visualization tools. Check APIs & Integrations for details.

cube(`orders`, {
  // ...
 
  measures: {
    total_amount: {
      sql: `amount`,
      type: `sum`,
      format: `$,.2f`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    measures:
      - name: total_amount
        sql: amount
        type: sum
        format: "$,.2f"

Common format examples

Format stringDescriptionExample output
.2fFixed-point, 2 decimal places1234.57
,.0fGrouped thousands, no decimals1,235
$,.2fCurrency with grouped thousands$1,234.57
.0%Percentage, no decimals12%
.2sSI prefix, 2 significant digits1.2k
+.2fExplicit sign, 2 decimal places+1234.57
.2~fTrimmed trailing zeros, 2 decimals1234.5

Format specifier syntax

The full syntax for a d3-format specifier is:

[[fill]align][sign][symbol][0][width][,][.precision][~][type]
ComponentDescription
fillCharacter to use for padding
align> (right), < (left), ^ (center), = (pad after sign)
sign- (negative only, default), + (positive and negative), ( (parentheses for negative)
symbol$ (currency prefix), # (alternate form for binary/octal/hex)
0Enable zero-padding (shorthand for fill=0 with align==)
widthMinimum field width
,Enable comma grouping separator
.precisionNumber of digits after the decimal point (for f, %) or significant digits (for other types)
~Trim trailing zeros
typeOutput format type (see table below)

Supported type characters

TypeDescription
fFixed-point notation
eExponent notation
gEither decimal or exponent notation, rounded to significant digits
rDecimal notation, rounded to significant digits
sDecimal notation with an SI prefix, rounded to significant digits
%Multiply by 100 and format with f, followed by percent sign
pMultiply by 100, round to significant digits, followed by percent sign
dInteger; ignores non-integer values
bBinary
oOctal
xHexadecimal (lowercase)
XHexadecimal (uppercase)
cCharacter data (converts integer to Unicode)
(none)Similar to g, but trims trailing zeros

Dimension Types

This section describes the various types that can be assigned to a dimension. A dimension can only have one type.

time

In order to be able to work with time series data, Cube needs to identify a time dimension which is a timestamp column in your database. You can define several time dimensions in a single cube.

cube(`orders`, {
  // ...
 
  dimensions: {
    completed_at: {
      sql: `completed_at`,
      type: `time`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: completed_at
        sql: completed_at
        type: time

Note that the type of the target column should be TIMESTAMP. If your time-based column is type DATE or another temporal type, you should cast it to a timestamp in the sql parameter of the dimension.

Please see this recipe if your datetime information is stored as a string.

string

string is typically used with fields that contain letters or special characters.

The sql parameter is required and must include any valid SQL expression with an aggregate function that returns a value of the string type.

The following model creates a field full_name by combining 2 fields: first_name and last_name:

cube(`orders`, {
  // ...
 
  dimensions: {
    full_name: {
      sql: `CONCAT(${first_name}, ' ', ${last_name})`,
      type: `string`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: full_name
        sql: "CONCAT({first_name}, ' ', {last_name})"
        type: string

number

number is typically used with fields that contain number or integer.

cube(`orders`, {
  // ...
 
  dimensions: {
    amount: {
      sql: `amount`,
      type: `number`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: amount
        sql: amount
        type: number

boolean

boolean is used with fields that contain boolean data or data coercible to boolean. For example:

cube(`orders`, {
  // ...
 
  dimensions: {
    is_enabled: {
      sql: `is_enabled`,
      type: `boolean`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: is_enabled
        sql: is_enabled
        type: boolean

switch

switch type is used to define a dimension that can only take one of the predefined set of values. It is similar to an enum type in programming languages. They are particularly useful for defining case measures.

switch dimensions 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 for the dimension of this type.

cube(`orders`, {
  // ...
 
  dimensions: {
    currency: {
      type: `switch`,
      values: [
        `USD`,
        `EUR`,
        `GBP`
      ]
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: currency
        type: switch
        values:
          - USD
          - EUR
          - GBP

When switch dimensions are queried or introspected using the /v1/meta REST API endpoint, they are represented as dimensions of type string.

geo

geo dimension is used to display data on the map. Unlike other dimension types it requires to set two fields: latitude and longitude.

cube(`orders`, {
  // ...
 
  dimensions: {
    location: {
      type: `geo`,
      latitude: {
        sql: `${CUBE}.latitude`
      },
      longitude: {
        sql: `${CUBE}.longitude`
      }
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: location
        type: geo
        latitude:
          sql: "{CUBE}.latitude"
        longitude:
          sql: "{CUBE}.longitude"

Dimension Formats

imageUrl

imageUrl is used for displaying images in table visualization. In this case sql parameter should contain full path to the image.

cube(`orders`, {
  // ...
 
  dimensions: {
    image: {
      sql: `CONCAT('https://img.example.com/id/', ${id})`,
      type: `string`,
      format: `imageUrl`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: image
        sql: "CONCAT('https://img.example.com/id/', {id})"
        type: string
        format: imageUrl

id

id is used for IDs. It allows to eliminate applying of comma for 5+ digit numbers which is default for type number. The sql parameter is required and can take any valid SQL expression.

cube(`orders`, {
  // ...
 
  dimensions: {
    image: {
      sql: `id`,
      type: `number`,
      format: `id`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: image
        sql: id
        type: number
        format: id

link is used for creating hyperlinks. link parameter could be either String or Object. Use Object, when you want to give a specific label to link. See examples below for details.

The sql parameter is required and can take any valid SQL expression.

cube(`orders`, {
  // ...
 
  dimensions: {
    order_link: {
      sql: `'http://myswebsite.com/orders/' || id`,
      type: `string`,
      format: `link`
    },
 
    crm_link: {
      sql: `'https://na1.salesforce.com/' || id`,
      type: `string`,
      format: {
        label: `View in Salesforce`,
        type: `link`
      }
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: order_link
        sql: "'http://myswebsite.com/orders/' || id"
        type: string
        format: link
 
      - name: crm_link
        sql: "'https://na1.salesforce.com/' || id"
        type: string
        format:
          label: View in Salesforce
          type: link

currency

currency is used for monetary values.

cube(`orders`, {
  // ...
 
  dimensions: {
    amount: {
      sql: `amount`,
      type: `number`,
      format: `currency`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: amount
        sql: amount
        type: number
        format: currency

percent

percent is used for formatting numbers with a percent symbol.

cube(`orders`, {
  // ...
 
  dimensions: {
    open_rate: {
      sql: `COALESCE(1.0 * ${uniq_open_count} / NULLIF(${delivered_count}, 0), 0)`,
      type: `number`,
      format: `percent`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: open_rate
        sql:
          "COALESCE(1.0 * {uniq_open_count} / NULLIF({delivered_count}, 0), 0)"
        type: number
        format: percent

Custom numeric formats

Dimensions with the number type support custom formatting using d3-format  specifier strings.

Custom numeric formatting is subject to support in visualization tools. Check APIs & Integrations for details.

cube(`orders`, {
  // ...
 
  dimensions: {
    amount: {
      sql: `amount`,
      type: `number`,
      format: `$,.2f`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: amount
        sql: amount
        type: number
        format: "$,.2f"

See the Custom numeric formats section under Measure Formats for the full format specifier syntax and supported type characters.

Custom time formats

Dimensions with time type support custom formatting using POSIX strftime  format strings with d3-time-format  extensions.

Custom time formatting is subject to support in visualization tools. Check APIs & Integrations for details.

cube(`orders`, {
  // ...
 
  dimensions: {
    created_at: {
      sql: `created_at`,
      type: `time`,
      format: `%Y-%m-%d %H:%M:%S`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: created_at
        sql: created_at
        type: time
        format: "%Y-%m-%d %H:%M:%S"

Common format examples

Format stringExample output
%m/%d/%Y %H:%M12/04/2025 14:30
%Y-%m-%d %H:%M:%S2025-12-04 14:30:00
%B %d, %YDecember 04, 2025
%b %d, %YDec 04, 2025
%I:%M %p02:30 PM
%A, %B %dThursday, December 04
Q%q %YQ4 2025
Week %V, %YWeek 49, 2025

Supported format specifiers

SpecifierDescription
%aAbbreviated weekday name
%AFull weekday name
%bAbbreviated month name
%BFull month name
%cLocale’s date and time
%dDay of month [01,31]
%eSpace-padded day of month
%fMicroseconds
%gISO 8601 year without century
%GISO 8601 year with century
%HHour (24-hour) [00,23]
%IHour (12-hour) [01,12]
%jDay of year [001,366]
%LMilliseconds
%mMonth [01,12]
%MMinute [00,59]
%pAM or PM
%qQuarter [1,4]
%QMilliseconds since UNIX epoch
%sSeconds since UNIX epoch
%SSecond [00,61]
%uMonday-based weekday [1,7]
%USunday-based week number [00,53]
%VISO 8601 week number
%wSunday-based weekday [0,6]
%WMonday-based week number [00,53]
%xLocale’s date
%XLocale’s time
%yYear without century [00,99]
%YYear with century
%ZTime zone name
%%Literal percent sign

Was this page useful?

Access policiesContext variables