Measure Types

LookML
Version

On this Page
Docs Menu

Go to Field Parameter List

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

view: view_name {
  measure: field_name {
    type: measure_field_type
  }
}

Hierarchy

type

Possible Field Types

Measure

Accepts

A 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 data
Read 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:

- measure: category_list type: string sql: GROUP_CONCAT(${category})
measure: category_list { type: string sql: GROUP_CONCAT(${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:

- measure: most_recent_order_date type: date sql: MAX(${order_date}) convert_tz: false
measure: most_recent_order_date { type: date sql: MAX(${order_date}) ;; convert_tz: no }

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:

- measure: change_in_inventory type: int sql: ${number_of_items_sold} - ${number_of_items_returned}
measure: change_in_inventory { type: int sql: ${number_of_items_sold} - ${number_of_items_returned} ;; }

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

- measure: total_profit type: number sql: ${total_revenue} - ${total_cost} value_format_name: usd
measure: total_profit { type: number sql: ${total_revenue} - ${total_cost} ;; value_format_name: usd }

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:

- view: products fields: - measure: number_of_products type: count drill_fields: product_details* # optional
view: products { measure: number_of_products { type: count drill_fields: [product_details*] # optional } }

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:

- measure: number_of_unique_customers type: count_distinct sql: ${customer_id}
measure: number_of_unique_customers { type: count_distinct sql: ${customer_id} ;; }

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

- measure: total_revenue type: sum sql: ${sales_price} value_format_name: usd
measure: total_revenue { type: sum sql: ${sales_price} ;; value_format_name: usd }

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:

view: view_name {
  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.

# Will NOT calculate the correct shipping amount - measure: total_shipping type: sum sql: ${order_shipping}
# Will NOT calculate the correct shipping amount measure: total_shipping { type: sum sql: ${order_shipping} ;; }

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:

# Will calculate the correct shipping amount - measure: total_shipping type: sum_distinct sql_distinct_key: ${order_id} sql: ${order_shipping}
# Will calculate the correct shipping amount measure: total_shipping { type: sum_distinct sql_distinct_key: ${order_id} ;; sql: ${order_shipping} ;; }

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

- measure: avg_order type: average sql: ${sales_price} value_format_name: usd
measure: avg_order { type: average sql: ${sales_price} ;; value_format_name: usd }

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.

# Will NOT calculate the correct average - measure: avg_shipping type: average sql: ${order_shipping}
# Will NOT calculate the correct average measure: avg_shipping { type: average sql: ${order_shipping} ;; }

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:

# Will calculate the correct average - measure: avg_shipping type: average_distinct sql_distinct_key: ${order_id} sql: ${order_shipping}
# Will calculate the correct average measure: avg_shipping { type: average_distinct sql_distinct_key: ${order_id} ;; sql: ${order_shipping} ;; }

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

- measure: median_order type: median sql: ${sales_price} value_format_name: usd
measure: median_order { type: median sql: ${sales_price} ;; value_format_name: usd }

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

- measure: median_shipping type: median_distinct sql_distinct_key: ${order_id} sql: ${order_shipping}
measure: median_shipping { type: median_distinct sql_distinct_key: ${order_id} ;; sql: ${order_shipping} ;; }

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. The following list shows which dialects support the median_distinct type in the most current release:

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

- measure: smallest_order type: min sql: ${sales_price} value_format_name: usd
measure: smallest_order { type: min sql: ${sales_price} ;; value_format_name: usd }

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:

- measure: earliest_name_in_alphabet type: string sql: MIN(${name})
measure: earliest_name_in_alphabet { type: string sql: MIN(${name}) ;; }

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

- measure: largest_order type: max sql: ${sales_price} value_format_name: usd
measure: largest_order { type: max sql: ${sales_price} ;; value_format_name: usd }

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:

- measure: latest_name_in_alphabet type: string sql: MAX(${name})
measure: latest_name_in_alphabet { type: string sql: MAX(${name}) ;; }

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:

view: view_name {
  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:

- measure: name_list type: list list_field: name
measure: name_list { type: list list_field: name }

Please note that type: list measures cannot be filtered on.

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 current release:

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:

- measure: count_growth type: percent_of_previous sql: ${count}
measure: count_growth { type: percent_of_previous sql: ${count} ;; }

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:

- measure: percent_of_total_gross_margin type: percent_of_total sql: ${total_gross_margin}
measure: percent_of_total_gross_margin { type: percent_of_total sql: ${total_gross_margin} ;; }

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:

- measure: test_scores_75th_percentile type: percentile percentile: 75 sql: ${TABLE}.test_scores
measure: test_scores_75th_percentile { type: percentile percentile: 75 sql: ${TABLE}.test_scores ;; }

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 current release:

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:

- measure: order_shipping_90th_percentile type: percentile_distinct percentile: 90 sql_distinct_key: ${order_id} sql: ${order_shipping}
measure: order_shipping_90th_percentile { type: percentile_distinct percentile: 90 sql_distinct_key: ${order_id} ;; sql: ${order_shipping} ;; }

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 in the most current release:

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:

- measure: cumulative_total_revenue type: running_total sql: ${total_sale_price} value_format_name: usd
measure: cumulative_total_revenue { type: running_total sql: ${total_sale_price} ;; value_format_name: usd }

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.

Still have questions?
Go to Discourse - or - Email Support
Top