This page refers to the

`type`

parameter that is part of a measure.

`type`

can also be used as part of a dimension or filter, described on this documentation page.

`type`

can also be used as part of a dimension group, described on this documentation page.

# Usage

measure: field_name {

type: measure_field_type

}

}

## Hierarchy`type` |
## Possible Field TypesMeasure## AcceptsA measure type |

This page includes details about the various types that can be assigned to a measure. A measure can only have one type, and defaults to `string`

if no type is specified.

Some measure types have supporting parameters, which are described within the appropriate section.

# Measure Type Categories

Each measure type falls into one of the following categories. These categories determine whether the measure type performs aggregations, the type of fields that the measure type can reference, and whether you can filter the measure type using the `filters`

parameter:

**Aggregate measures**: Aggregate measure types perform aggregations, such as`sum`

and`average`

. Aggregate measures can reference only dimensions, not other measures. This is the only measure type that works with the`filters`

parameter.**Non-aggregate measures**: Non-aggregate measures are, as the name suggests, measure types that do not perform aggregations, such as`number`

and`yesno`

. These measure types perform simple transformations, and since they do not perform aggregations, can reference only aggregate measures or previously-aggregated dimensions. You cannot use the`filters`

parameter with these measure types.**Post-SQL measures**: Post-SQL measures are special measure types that perform specific calculations after Looker has generated query SQL. They can reference only numeric measures or numeric dimensions. You cannot use the`filters`

parameter with these measure types.**List measure**:`list`

is a specific measure type that does not perform aggregation; rather, it creates a list of distinct values in a dimension.`list`

can reference only a dimension, and does not support the`filters`

parameter.

# List of Type Definitions

Type | Category | Description |
---|---|---|

`average` |
Aggregate | Generates an average (mean) of values within a column |

`average_distinct` |
Aggregate | Properly generates an average (mean) of values when using denormalized data. See the definition below for a complete description. |

`count` |
Aggregate | Generates a count of rows |

`count_distinct` |
Aggregate | Generates a count of unique values within a column |

`date` |
Non-aggregate | For measures that contain dates |

`list` |
List | Generates a list of the unique values within a column |

`max` |
Aggregate | Generates the maximum value within a column |

`median` |
Aggregate | Generates the median (midpoint value) of values within a column |

`median_distinct` |
Aggregate | Properly generates a median (midpoint value) of the values when a join causes a fanout. See the definition below for a complete description. |

`min` |
Aggregate | Generates the minimum value within a column |

`number` |
Non-aggregate | For measures that contain numbers |

`percent_of_previous` |
Post-SQL | Generates the percent difference between displayed rows |

`percent_of_total` |
Post-SQL | Generates the percent of total for each displayed row |

`percentile` |
Aggregate | Generates the value at the specified percentile within a column |

`percentile_distinct` |
Aggregate | Properly generates the value at the specified percentile when a join causes a fanout. See the definition below for a complete description. |

`running_total` |
Post-SQL | Generates the running total for each displayed row |

`string` |
Non-aggregate | For measures that contain letters or special characters (as with MySQL’s `GROUP_CONCAT` function) |

`sum` |
Aggregate | Generates a sum of values within a column |

`sum_distinct` |
Aggregate | Properly generates a sum of values when using denormalized dataSee the definition below for a complete description. |

`yesno` |
Non-aggregate | For fields that will show if something is true or false |

`int` |
Non-aggregate | REMOVED5.4 Replaced by `type: number` |

`average`

`type: average`

averages the values in a given field. It is similar to SQL’s `AVG`

function. However, unlike writing raw SQL, Looker will properly calculate averages even if your query’s joins contain fanouts.

The `sql`

parameter for `type: average`

measures can take any valid SQL expression that results in a numeric table column, LookML dimension, or combination of LookML dimensions.

`type: average`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

For example, the following LookML creates a field called `avg_order`

by averaging the `sales_price`

dimension, then displays it in a money format ($1,234.56):

`average_distinct`

`type: average_distinct`

is for use with denormalized datasets. It averages the non-repeated values in a given field, based on the unique values defined by the `sql_distinct_key`

parameter.

This is an advanced concept which may be more clearly explained with an example. Consider a denormalized table like this:

Order Item ID | Order ID | Order Shipping |
---|---|---|

1 | 1 | 10.00 |

2 | 1 | 10.00 |

3 | 2 | 20.00 |

4 | 2 | 20.00 |

5 | 2 | 20.00 |

In this situation you can see that there are multiple rows for each order. Consequently, if you added a simple `type: average`

measure for the `order_shipping`

column, you would get a value of 16.00, even though the actual average is 15.00.

To get an accurate result, you can explain to Looker how it should identify each unique entity (in this case, each unique order) by using the `sql_distinct_key`

parameter. This *will* calculate the correct 15.00 amount:

Please note that every unique value of `sql_distinct_key`

must have just one, corresponding value in `sql`

. In other words, the above example works because *every* row with an `order_id`

of 1 has the same `order_shipping`

of 10.00, *every* row with an `order_id`

of 2 has the same `order_shipping`

of 20.00, and so on.

`type: average_distinct`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

`count`

`type: count`

performs a table count, similar to SQL’s `COUNT`

function. However, unlike writing raw SQL, Looker will properly calculate counts even if your query’s joins contain fanouts.

`type: count`

measures do not support the `sql`

parameter, as a `type: count`

measure performs table counts based on the table’s primary key. If you want to perform a table count on a field other than the table’s primary key, use a `type: count_distinct`

measure.

For example, the following LookML creates a field `number_of_products`

:

It is very common to provide a `drill_fields`

parameter when defining a `type: count`

measure, so that users can see the individual records that make up a count when they click on it.

When you use a measure of

`type: count`

in an Explore, the visualization labels the resulting values with the view name rather than the word “Count.” To avoid confusion, we recommend pluralizing your view name, selectingShow Full Field NameunderSeriesin the visualization settings, or using a`view_label`

with a pluralized version of your view name.

`count_distinct`

`type: count_distinct`

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

function.

The `sql`

parameter for `type: count_distinct`

measures can take any valid SQL expression that results in a table column, LookML dimension, or combination of LookML dimensions.

For example, the following LookML creates a field `number_of_unique_customers`

, which counts the number of unique customer IDs:

`date`

`type: date`

is used with fields that contain dates.

The `sql`

parameter for `type: date`

measures can take any valid SQL expression that results in a date. In practice, this type is rarely used, because most SQL aggregate functions do not return dates. One common exception is a `MIN`

or `MAX`

of a date dimension.

For example, the following LookML creates a field `most_recent_order_date`

by taking the maximum value from the `order_date`

field:

In this example `type: date`

could be omitted, and the value would be treated as a string, because `string`

is the default value for `type`

. However, you will get better filtering capability for users if you use `type: date`

. The `convert_tz`

parameter prevents double time zone conversion. This concept is described in more detail in this Community topic.

`list`

`type: list`

creates a list of the distinct values in a given field. It is similar to MySQL’s `GROUP_CONCAT`

function.

You do not need to include a `sql`

parameter for `type: list`

measures. Instead, you’ll use the `list_field`

parameter to specify the dimension from which you want to create lists.

The usage is:

measure: field_name {

type: list

list_field: my_field_name

}

}

For example, the following LookML creates a measure `name_list`

based on the `name`

dimension:

`type: list`

measures cannot be filtered on. As of Looker 6.8, field references are also not supported for measures of `type: list`

.

## Supported Database Dialects for `list`

Looker’s ability to provide `type: list`

depends on the database dialect’s functionality. The following list shows which dialects support `type:list`

in the most recent Looker release:

`max`

`type: max`

finds the largest value in a given field. It makes use of SQL’s `MAX`

function.

The `sql`

parameter for `type: max`

measures can take any valid SQL expression that results in a numeric table column, LookML dimension, or combination of LookML dimensions. It will **not** work with dates; instead, see the example in `type: date`

above.

`type: max`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

For example, the following LookML creates a field called `largest_order`

by looking at the `sales_price`

dimension, then displays it in a money format ($1,234.56):

You cannot currently use `type: max`

measures for strings or dates, but you can manually add the `MAX`

function to create such a field, like this:

`median`

`type: median`

returns the midpoint value for the values in a given field. This is especially useful when the data has a few very large or small outlier values that would skew a simple average (mean) of the data.

Consider a table like this:

Order Item ID | Cost | Midpoint? |
---|---|---|

2 | 10.00 | |

4 | 10.00 | |

3 | 20.00 | Midpoint value |

1 | 80.00 | |

5 | 90.00 |

For easy viewing, the table is sorted by cost but that does not affect the result. While the `average`

type would return 42 (adding all the values and dividing by 5), the `median`

type would return the midpoint value: 20.00.

If there is an even number of values, then the median value is calculated by taking the mean of the two values closest to the midpoint. Consider a table like this with an even number of rows:

Order Item ID | Cost | Midpoint? |
---|---|---|

2 | 10 | |

3 | 20 | Closest before midpoint |

1 | 80 | Closest after midpoint |

4 | 90 |

The median, the middle value, is `(20 + 80)/2 = 50`

.

The median is also equal to the value at the 50th percentile.

The `sql`

parameter for `type: median`

measures can take any valid SQL expression that results in a numeric table column, LookML dimension, or combination of LookML dimensions.

`type: median`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

## Example

For example, the following LookML creates a field called `median_order`

by averaging the `sales_price`

dimension, then displays it in a money format ($1,234.56):

## Things to Consider for `median`

If you are using `median`

for a field involved in a fanout, Looker will attempt to use `median_distinct`

instead.

If `median_distinct`

is not available for the dialect, Looker returns an error. Since the `median`

can be considered the 50th percentile, the error states that the dialect does not support distinct percentiles. For more information, see supported dialects for `median_distinct`

.

## Supported Database Dialects for `median`

Looker’s ability to provide a `median`

type depends on the database dialect’s functionality. The following list shows which dialects support the `median`

type in the most recent Looker release:

When there is a fanout involved in a query, Looker tries to convert the `median`

into `median_distinct`

. This is only successful in dialects that support `median_distinct`

.

`median_distinct`

Use `type: median_distinct`

when your join involves a fanout. It averages the nonrepeated values in a given field, based on the unique values defined by the `sql_distinct_key`

parameter. If the measure does not have a `sql_distinct_key`

parameter, then Looker tries to use the `primary_key`

field.

Consider the result of a query joining the Order Item and Order tables:

Order Item ID | Order ID | Order Shipping |
---|---|---|

1 | 1 | 10 |

2 | 1 | 10 |

3 | 2 | 20 |

4 | 3 | 50 |

5 | 3 | 50 |

6 | 3 | 50 |

In this situation you can see that there are multiple rows for each order. This query involved a fanout because each order maps to several order items. The `median_distinct`

takes this into consideration and finds the median between the distinct values 10, 20, and 50 so you would get a value of 20.

To get an accurate result, you can explain to Looker how it should identify each unique entity (in this case, each unique order) by using the `sql_distinct_key`

parameter. This will calculate the correct amount:

Please note that every unique value of `sql_distinct_key`

must have just one corresponding value in the measure’s `sql`

parameter. In other words, the above example works because *every* row with an `order_id`

of 1 has the same `order_shipping`

of 10, *every* row with an `order_id`

of 2 has the same `order_shipping`

of 20, and so on.

`type: median_distinct`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

## Things to Consider for `median_distinct`

If `median_distinct`

is not available for the dialect, Looker returns an error. Since the `median`

can be considered the 50th percentile, the error states that the dialect does not support distinct percentiles. For more information, see supported dialects for `median_distinct`

in the most recent Looker release:

## Supported Database Dialects for `median_distinct`

Looker’s ability to provide a `median_distinct`

type depends on the database dialect’s functionality. The following list shows which dialects support the `median_distinct`

type.

`min`

`type: min`

finds the smallest value in a given field. It makes use of SQL’s `MIN`

function.

The `sql`

parameter for `type: min`

measures can take any valid SQL expression that results in a numeric table column, LookML dimension, or combination of LookML dimensions. It will **not** work with dates; instead, see the example in `type: date`

above.

`type: min`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

For example, the following LookML creates a field called `smallest_order`

by looking at the `sales_price`

dimension, then displays it in a money format ($1,234.56):

You cannot currently use `type: min`

measures for strings or dates, but you can manually add the `MIN`

function to create such a field, like this:

`number`

`type: number`

is used with numbers or integers. A measure of `type: number`

does not perform any aggregation, and is meant to perform simple transformations on other measures. If you are defining a measure based on another measure, the new measure must be of `type: number`

to avoid nested-aggregation errors.

The `sql`

parameter for `type: number`

measures can take any valid SQL expression that results in a number or integer.

`type: number`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

For example, the following LookML creates a measure called `total_gross_margin_percentage`

based on the `total_sale_price`

and `total_gross_margin`

aggregate measures, then displays it in a percentage format with two decimals (12.34%):

The example above also uses the `NULLIF()`

SQL function to remove the possibility of division-by-zero errors.

## Things to Consider for `type: number`

There are several important things to keep in mind when using `type: number`

measures:

- A measure of
`type: number`

can perform arithmetic only on other measures, not on other dimensions. - Looker’s symmetric aggregates will not protect aggregate functions in the SQL of a measure
`type: number`

when computed across a join. - The
`filters`

parameter cannot be used with`type: number`

measures, but the`filters`

documentation explains a workaround. `type: number`

measures will not provide suggestions to users.

`percent_of_previous`

`type: percent_of_previous`

calculates the percent difference between a cell and the previous cell in its column.

The `sql`

parameter for `type: percent_of_previous`

measures must reference another, numeric measure.

`type: percent_of_previous`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

In the following example, this LookML creates a measure `count_growth`

based on the `count`

measure:

In the Looker UI, this would look like:

Note that `percent_of_previous`

values depend on sort order. If you change the sort, you must rerun the query to recalculate the `percent_of_previous`

values. In cases where a query is pivoted, `percent_of_previous`

runs across the row instead of down the column. You cannot currently change this behavior.

Additionally, `percent_of_previous`

measures are calculated *after* data is returned from your database. This means that you should not reference a `percent_of_previous`

measure within another measure; since they might be calculated at different times, you may not get accurate results. It also means that `percent_of_previous`

measures cannot be filtered on.

`percent_of_total`

`type: percent_of_total`

calculates a cell’s portion of the column total. Note that the percentage is being calculated against the total of the *displayed* rows, and *not* the total of all possible rows.

The `sql`

parameter for `type: percent_of_total`

measures must reference another, numeric measure.

`type: percent_of_total`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

In the following example, this LookML creates a measure `percent_of_total_gross_margin`

based on the `total_gross_margin`

measure:

In the Looker UI, this would look like:

In cases where a query is pivoted, `percent_of_total`

runs across the row instead of down the column. If this is not desired, add `direction: "column"`

to the measure definition.

Additionally, `percent_of_total`

measures are calculated *after* data is returned from your database. This means that you should not reference a `percent_of_total`

measure within another measure; since they might be calculated at different times, you may not get accurate results. It also means that `percent_of_total`

measures cannot be filtered on.

`percentile`

`type: percentile`

returns the value at the specified percentile of values in a given field. For example, specifying the 75th percentile will return the value that is greater than 75% of the other values in the dataset.

To identify the value to return, Looker calculates the total number of data values and multiplies the specified percentile times the total number of data values. Regardless of how the data is actually sorted, Looker identifies the data values’ relative order in increasing value. The data value that Looker returns depends on whether the calculation results in an integer or not, as discussed below.

## If the Calculated Value Is Not an Integer

Looker rounds the calculated value up and uses it to identify the data value to return. In this example set of 19 test scores, the 75th percentile would be identified by 19 * .75 = 14.25, which means that 75% of the values are in the first 14 data values — below the 15th position. Thus, Looker returns the 15th data value (87) as being larger than 75% of the data values.

## If the Calculated Value Is an Integer

In this slightly more complex case, Looker returns an average of the data value at that position and the following data value. To understand this, consider a set of 20 test scores, the 75th percentile would be identified by 20 * .75 = 15, which means that the data value at the 15th position is part of the 75th percentile and we need to return a value that is *above* 75% of the data values. By returning the average of the values at the 15th position (82) and the 16th position (87), Looker ensures that 75%. That average (84.5) does not exist in the set of data values but would be larger than 75% of the data values.

## Required and Optional Parameters

Use the `percentile:`

keyword to specify the fractional value, meaning the percent of the data that should be below the returned value. For example, use `percentile: 75`

to specify the value at the 75th percentile in the order of data, or `percentile: 10`

to return the value at the 10th percentile. If you want to find the value at the 50th percentile, you can specify `percentile: 50`

or simply use the median type.

The `sql`

parameter for `type: percentile`

measures can take any valid SQL expression that results in a numeric table column, LookML dimension, or combination of LookML dimensions.

`type: percentile`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

## Example

For example, the following LookML creates a field called `test_scores_75th_percentile`

which returns the value at the 75th percentile in the `test_scores`

dimension:

## Things to Consider for `percentile`

If you are using `percentile`

for a field involved in a fanout, Looker will attempt to use `percentile_distinct`

instead. If `percentile_distinct`

is not available for the dialect, Looker returns an error. For more information, see supported dialects for `percentile_distinct`

.

## Supported Database Dialects for `percentile`

Looker’s ability to provide a `percentile`

type depends on the database dialect’s functionality. The following list shows which dialects support the `percentile`

type in the most recent Looker release:

`percentile_distinct`

The `type: percentile_distinct`

is a specialized form of percentile and should be used when your join involves a fanout. It uses the non-repeated values in a given field, based on the unique values defined by the `sql_distinct_key`

parameter. If the measure does not have a `sql_distinct_key`

parameter, then Looker tries to use the `primary_key`

field.

Consider the result of a query joining the Order Item and Order tables:

Order Item ID | Order ID | Order Shipping |
---|---|---|

1 | 1 | 10 |

2 | 1 | 10 |

3 | 2 | 20 |

4 | 3 | 50 |

5 | 3 | 50 |

6 | 3 | 50 |

7 | 4 | 70 |

8 | 4 | 70 |

9 | 5 | 110 |

10 | 5 | 110 |

In this situation you can see that there are multiple rows for each order. This query involved a fanout because each order maps to several order items. The `percentile_distinct`

takes this into consideration and finds the percentile value using the distinct values 10, 20, 50, 70, and 110. The 25th percentile will return the second distinct value, or 20, while the 80th percentile will return the average of the fourth and fifth distinct values, or 90.

## Required and Optional Parameters

Use the `percentile:`

keyword to specify the fractional value. For example, use `percentile: 75`

to specify the value at the 75th percentile in the order of data, or `percentile: 10`

to return the value at the 10th percentile. If you are trying to find the value at the 50th percentile, you can use the `median_distinct`

type instead.

To get an accurate result, specify how Looker should identify each unique entity (in this case, each unique order) by using the `sql_distinct_key`

parameter.

Here’s an example of using `percentile_distinct`

to return the value at the 90th percentile:

Please note that every unique value of `sql_distinct_key`

must have just one corresponding value in the measure’s `sql`

parameter. In other words, the above example works because *every* row with `order_id`

of 1 has the same `order_shipping`

of 10, *every* row with an `order_id`

of 2 has the same `order_shipping`

of 20, and so on.

`type: percentile_distinct`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

## Things to Consider for `percentile_distinct`

If `percentile_distinct`

is not available for the dialect, Looker returns an error. For more information, see supported dialects for `percentile_distinct`

.

## Supported Database Dialects for `percentile_distinct`

Looker’s ability to provide a `percentile_distinct`

type depends on the database dialect’s functionality. The following list shows which dialects support the `percentile_distinct`

type.

`running_total`

`type: running_total`

calculates a cumulative sum of the cells along a column. It cannot be used to calculate sums along a row, unless the row has resulted from a pivot.

The `sql`

parameter for `type: running_total`

measures must reference another, numeric measure.

`type: running_total`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

For example, the following LookML creates a measure `cumulative_total_revenue`

based on the `total_sale_price`

measure:

In the Looker UI, this would look like:

Note that `running_total`

values depend on sort order. If you change the sort, you must re-run the query to re-calculate the `running_total`

values. In cases where a query is pivoted, `running_total`

runs across the row instead of down the column. If this is not desired, add `direction: "column"`

to the measure definition.

Additionally, `running_total`

measures are calculated *after* data is returned from your database. This means that you should not reference a `running_total`

measure within another measure; since they might be calculated at different times, you may not get accurate results. It also means that `running_total`

measures cannot be filtered on.

`string`

`type: string`

is used with fields that contain letters or special characters.

The `sql`

parameter for `type: string`

measures can take any valid SQL expression that results in a string. In practice, this type is rarely used, because most SQL aggregate functions do not return strings. One common exception is MySQL’s `GROUP_CONCAT`

function, although Looker provides `type: list`

for that use case.

For example, the following LookML creates a field `category_list`

by combining the unique values of a field called `category`

:

In this example `type: string`

could be omitted, because `string`

is the default value for `type`

.

`sum`

`type: sum`

adds up the values in a given field. It is similar to SQL’s `SUM`

function. However, unlike writing raw SQL, Looker will properly calculate sums even if your query’s joins contain fanouts.

The `sql`

parameter for `type: sum`

measures can take any valid SQL expression that results in a numeric table column, LookML dimension, or combination of LookML dimensions.

`type: sum`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

For example, the following LookML creates a field called `total_revenue`

by adding up the `sales_price`

dimension, then displays it in a money format ($1,234.56):

`sum_distinct`

`type: sum_distinct`

is for use with denormalized datasets. It adds up the non-repeated values in a given field, based on the unique values defined by the `sql_distinct_key`

parameter.

The usage is:

measure: field_name {

type: sum_distinct

sql_distinct_key: ${my_field_name} ;;

}

}

This is an advanced concept which may be more clearly explained with an example. Consider a denormalized table like this:

Order Item ID | Order ID | Order Shipping |
---|---|---|

1 | 1 | 10.00 |

2 | 1 | 10.00 |

3 | 2 | 20.00 |

4 | 2 | 20.00 |

5 | 2 | 20.00 |

In this situation you can see that there are multiple rows for each order. Consequently, if you added a simple `type: sum`

measure for the `order_shipping`

column, you would get a total of 80.00, even though the total shipping collected is actually 30.00.

To get an accurate result, you can explain to Looker how it should identify each unique entity (in this case, each unique order) by using the `sql_distinct_key`

parameter. This *will* calculate the correct 30.00 amount:

Please note that every unique value of `sql_distinct_key`

must have just one, corresponding value in `sql`

. In other words, the above example works because *every* row with an `order_id`

of 1 has the same `order_shipping`

of 10.00, *every* row with an `order_id`

of 2 has the same `order_shipping`

of 20.00, and so on.

`type: sum_distinct`

fields can be formatted by using the `value_format`

or `value_format_name`

parameters.

`yesno`

`type: yesno`

creates a field that indicates if something is true or false. The values appear as **Yes** and **No** in the Explore UI.

The `sql`

parameter for a `type: yesno`

measure takes a valid SQL expression that evaluates to `TRUE`

or `FALSE`

. If the condition evaluates to `TRUE`

, **Yes** is displayed to the user; otherwise, **No** is displayed.

The SQL expression for `type: yesno`

measures must include only aggregations, which means SQL aggregations or references to LookML measures. If you want to create a `yesno`

field that includes a reference to a LookML dimension or a SQL expression that is not an aggregation, use a *dimension* with `type: yesno`

, not a measure.

Similar to measures with `type: number`

, a measure with `type: yesno`

doesn’t do any aggregations; it just references other aggregations.

For example, the `total_sale_price`

measure below is a sum of the total sale price of order items in an order. A second measure called `is_large_total`

is `type: yesno`

. The `is_large_total`

measure has a `sql`

parameter that evaluates whether the `total_sale_price`

value is greater than $1,000.

If you want to reference a `type: yesno`

field in another field, you should treat the `type: yesno`

field as a boolean (in other words, as if it contains a true or false value already). For example: