Measures of type: number are meant to be used to perform simple transformations on other measures. If you use a measure of type: number without aggregating any data, Looker will throw errors, or worse, fail to throw errors, but return bad data.

If Looker does throw an error it will probably be of the following form: column "field_name" must appear in the GROUP BY clause or be used in an aggregate function.

Example

If we defined some dimensions and measures in the following manner:

dimension: taxi_out_time {
  type: number
  sql: ${TABLE}.taxi_out ;;
}

dimension: taxi_in_time {
  type: number
  sql: ${TABLE}.taxi_in ;;
}

measure: type_number_to_break {
  # bad measure!
  type: number
  sql: ${taxi_out_time} - ${taxi_in_time} ;;
}

Looker will throw the following error if we attempt to create a query that includes taxi_in_time and our bad measure type_number_to_break:

This error occurs because of the way dimensions and measures map to columns in SQL.

A Looker dimension tells SQL that this column should appear in the GROUP BY clause, whereas a measure is meant to be an aggregate, and therefore should not appear in the GROUP BY clause.

So, when we declare a measure type: number without actually performing an aggregation, SQL excludes this column from the GROUP BY clause, when it in fact should be in the GROUP BY clause.

A Dangerous Edge Case

We note that Looker will not always throw an error. For example, if we run a query with all three of the fields we defined above Looker will not throw an error, because we happened to include both dimensions that the bad measure is based on, therefore everything is included in the GROUP BY clause. However, we note that the data is still not being aggregated, possibly leading to inaccurate data.

Things to Consider for type: number

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