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 withtype: number
measures, but thefilters
documentation explains a workaround. type: number
measures will not provide suggestions to users.