This section refers to the

`type`

parameter that is part of a measure

`type`

can also be used as part of a`dimension`

or`filter`

, described here

`type`

can also be used as part of a dimension_group, described here

# Usage

measure: field_name {

type: measure_field_type

}

}

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

This page includes additional 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.

## List of Type Definitions

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

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

`date` |
For measures that contain dates |

`int` |
AVOID AS OF3.38 Replaced by `type: number` |

`number` |
For measures that contain numbers |

`count` |
Generates a count of rows |

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

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

`sum_distinct` |
Properly generates a sum of values when using de-normalized dataRead full documentation for complete description |

`avg` |
OLD LOOKML Generates an average (mean) of values within a column |

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

`avg_distinct` |
OLD LOOKML Properly generates an average (mean) of values when using de-normalized data. Read full documentation for complete description. |

`average_distinct` |
Properly generates an average of values when using de-normalized data. Read full documentation for complete description. |

`median` |
ADDED4.12 Generates the median (midpoint value) of values within a column |

`median_distinct` |
ADDED4.12 Properly generates a median (midpoint value) of the values when a join causes a fanout. Read full documentation for complete description. |

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

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

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

`percentile` |
ADDED4.12 Generates the value at the specified percentile within a column |

`percentile_distinct` |
ADDED4.12 Properly generates the value at the specified percentile when a join causes a fanout. Read full documentation for complete description. |

`percent_of_previous` |
Generates the percent difference between displayed rows |

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

`running_total` |
Generates the running total for each displayed row |

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

.

## 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 timezone conversion. This concept is described in more detail in this Discourse post.

## int AVOID AS OF3.38

As of version 3.38, use `type: number`

instead.

`type: int`

is used with numbers that you want to display with no commas or decimal places. The underlying data does *not* necessarily have to be an integer; it can also be a floating point number.

The `sql`

parameter for `type: int`

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

For example, the following LookML creates a field **Change In Inventory** by subtracting a field called **Number Of Items Returned** from **Number Of Items Sold**:

**Note:** A measure can only perform arithmetic on other measures, not dimensions. Also, the `filters`

parameter cannot be used with `type: int`

measures (see the `filters`

documentation for a workaround).

## number

`type: number`

is used with numbers or integers.

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 field called **Total Profit** based on the **Total Revenue** and **Total Cost** measures, then displays it in a money format ($1,234.56):

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

measures:

- A measure can only perform arithmetic on other measures, not other dimensions
- 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

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

You do not need to include a `sql`

parameter for `type: count`

measures.

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.

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

## 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 de-normalized data sets. 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 de-normalized 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.

## 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 de-normalized data sets. 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 de-normalized 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.

## median ADDED4.12

`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. As of the current release, Looker provides a `median`

type for the following dialects:

- Amazon Redshift
- Apache Spark 1.5+
- Apache Spark 2
- Google BigQuery

- Google BigQuery Standard SQL
- IBM Netezza
- MySQL

- Oracle
- PostgreSQL
- PrestoDB
- Snowflake

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 ADDED4.12

Use `type: median_distinct`

when your join involves a fanout. It averages 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 |

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`

.

#### Supported Database Dialects for `median_distinct`

Looker’s ability to provide a `median_distinct`

type depends on the database dialect’s functionality. As of the current release, Looker provides a `median_distinct`

type for the following dialects:

- Google BigQuery
- Google BigQuery Standard SQL
- MySQL
- PostgreSQL

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

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

## list

`type: list`

only works with some dialects, including MySQL, PostgreSQL and BigQuery Standard SQL

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

Please note that `type: list`

measures cannot be filtered on.

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

For example, the following 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 re-run the query to re-calculate 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.

For example, the following 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 ADDED4.12

`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 data set.

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 next step depends on whether the calculation results in an integer:

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

This is a slightly more complex case and 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 use 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. As of the current release, Looker provides a `percentile`

type for the following dialects:

- Amazon Redshift
- Apache Spark 1.5+
- Apache Spark 2
- Google BigQuery

- Google BigQuery Standard SQL
- MySQL
- Oracle

- PostgreSQL
- PrestoDB
- Snowflake

## percentile_distinct ADDED4.12

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. As of the current release, Looker provides a `percentile_distinct`

type for the following dialects:

- Google BigQuery
- Google BigQuery Standard SQL
- MySQL
- PostgreSQL

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