Documentation
Data Modeling
Reference
Types 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. In the example below, we create a `string` measure by converting a numerical value to a string:

YAML
JavaScript
``````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. In the example below, we create a `time` measure from an existing dimension:

YAML
JavaScript
``````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 example below adds an `is_completed` measure which only returns `true` if all orders have the `completed` status:

YAML
JavaScript
``````cubes:
- name: orders
# ...

measures:
- name: is_completed
sql: "BOOL_AND(status = 'completed')"
type: boolean``````

### number

The `sql` parameter is required and can take any valid SQL expression that results in a number or integer. Type `number` is usually used, when performing arithmetic operations on measures. Learn more about Calculated Measures.

YAML
JavaScript
``````cubes:
- name: orders
# ...

measures:
- name: purchases_ratio
sql: "{purchases} / {count} * 100.0"
type: number
format: percent``````

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

YAML
JavaScript
``````cubes:
- name: orders
# ...

measures:
- name: ratio
sql: "SUM({CUBE}.amount) / count(*)"
type: number``````

### count

Performs a table count, similar to SQL’s `COUNT` function. However, unlike writing raw SQL, Cube.js will properly calculate counts even if your query’s joins will produce row multiplication. You do not need to include a `sql` parameter for 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.

YAML
JavaScript
``````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 can take any valid SQL expression that results in a table column, or interpolated JavaScript expression.

YAML
JavaScript
``````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 can take any valid SQL expression.

YAML
JavaScript
``````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 can take any valid SQL expression that results in a numeric table column, or interpolated JavaScript expression. `sql` parameter should contain only expression to sum without actual aggregate function.

YAML
JavaScript
``````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 for type: average measures can take any valid SQL expression that results in a numeric table column, or interpolated JavaScript expression.

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

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

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

YAML
JavaScript
``````cubes:
- name: orders
# ...

measures:
- name: purchase_conversion
sql: "{purchase} / {checkout} * 100.0"
type: number
format: percent``````

### currency

`currency` is used for monetary values.

YAML
JavaScript
``````cubes:
- name: orders
# ...

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

## 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 create time series charts, Cube needs to identify time dimension which is a timestamp column in your database.

You can define several time dimensions in data models and apply each when creating charts. Note that type of target column should be `TIMESTAMP`. Please use this guide if your datetime information is stored as a string.

YAML
JavaScript
``````cubes:
- name: orders
# ...

dimensions:
- name: completed_at
sql: completed_at
type: time``````

### string

`string` is typically used with fields that contain letters or special characters. The `sql` parameter is required and can take any valid SQL expression.

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

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

YAML
JavaScript
``````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:

YAML
JavaScript
``````cubes:
- name: orders
# ...

dimensions:
- name: is_enabled
sql: is_enabled
type: boolean``````

### geo

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

YAML
JavaScript
``````cubes:
- name: orders
# ...

dimensions:
- name: location
type: geo
latitude:
sql: "{CUBE}.latitude"
longitude:
sql: "{CUBE}.longitude"``````

## Dimension Formats

### image_url

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

YAML
JavaScript
``````cubes:
- name: orders
# ...

dimensions:
- name: image
sql: "CONCAT('https://img.example.com/id/', {id})"
type: string
format: image_url``````

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

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

YAML
JavaScript
``````cubes:
- name: orders
# ...

dimensions:
sql: "'http://myswebsite.com/orders/' || id"
type: string

sql: "'https://na1.salesforce.com/' || id"
type: string
format:
label: View in Salesforce

### currency

`currency` is used for monetary values.

YAML
JavaScript
``````cubes:
- name: orders
# ...

dimensions:
- name: amount
sql: amount
type: number
format: currency``````

### percent

`percent` is used for formatting numbers with a percent symbol.

YAML
JavaScript
``````cubes:
- name: orders
# ...

dimensions:
- name: open_rate
sql:
"COALESCE(100.0 * {uniq_open_count} / NULLIF({delivered_count}, 0), 0)"
type: number
format: percent``````