ERROR: Measures with Looker aggregations (sum, average, min, max, list types) may not reference other measures.

Knowledge Drop

Last Tested: Mar 16, 2019
 

The Error:

You might see this error in the LookML validator or the explore.

There is a related error from the same issue: "Error: aggregate function calls may not have nested aggregate or window function".
 

What does this error mean?

This error is because of a SQL limitation: nested aggregation. Most SQL dialects cannot do the following:

SELECT user_id, AVG(SUM(sale_price)) FROM orders GROUP BY user_id

Common Causes / Fixes:

1.It's fine to have a measure that references another measure, but it must be type: number. That's because the original measure(s) that the new measure is based off is/are already doing an aggregation. Declaring a second aggregate function will cause nested aggregation. Instead, using type: number acts as a place holder so you can reference other measures or combinations of measures. Example:|

measure: order_count { # Base measure #1
type: count

sql: ${order_id} ;;

}

measure: cancelled_orders { # Base measure #2

type: count

filters: {

field: status

value: "Cancelled"

}

}

measure: percent_cancelled_orders { # New measure

type: number

sql: (1.0*${cancelled_orders})/ISNULL(${order_count},0) ;;

}

2.Attempting multiple levels of aggregation that require a derived table.

Example: say you want to know the average amount that customers spend in their customer lifetime, IE "Average Customer Lifetime Value". This requires two levels of aggregation:

  1. Sum(sales) group by customer.
  2. Average of that.

You might try:

measure: total_revenue {

type: sum

sql: ${sale_price} ;;

}

 

measure: avg_customer_lifetime_value {

type: average

sql: ${total_revenue} ;;

}

But this won't work because:

  1. Nested aggregation: SQL will err from: SELECT AVG(SUM(x) FROM ... , and would require a subquery instead: SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) s
  2. Measures are designed to be grouped dynamically by any dimension on the fly. In this example, customer_lifetime_value should always, by definition, be group by customer_id. Locking a measure's "group by" requires a derived table.

Solution: create a derived table so the measure total_lifetime_value becomes a dimension, then average it in a measure.

 

view: customer_facts {

derived_table: {

sql: SELECT user_id, COALESCE(SUM(sale_price), 0) AS customer_lifetime_value

FROM orders ;;

}

dimension: customer_lifetime_value {

type: number

sql: ${TABLE}."customer_lifetime_value" ;;

}


 

measure: average_customer_lifetime_value {

type: average

sql: ${customer_lifetime_value}

}

}

This content is subject to limited support.                

Version history
Last update:
‎04-05-2021 03:32 PM
Updated by: