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

While developing in a project, you might see an error like the following in an Explore or in the LookML Validator:

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

This error is caused by an aggregate measure referencing another aggregation or measure of any type in its LookML definition, such as:

    SELECT AVG((COUNT(*)) ) AS users.average_count FROM demo_db.users  AS users

SQL statements such as this generate a double or nested aggregation in SQL — most SQL dialects are unable to double aggregate, or nest aggregations, so such an attempt triggers the error.

Solutions

There are two possible solutions:

1. Use non-aggregate measures to perform non-aggregating SQL statements between measures.
2. Use a derived table to nest aggregations or double aggregate.

Using non-aggregate measures

Non-aggregate measures — such as type: yesno and type: number — are the only measures that can reference other measures or aggregations. Non-aggregate measures do not perform any aggregation and thus will not perform a double or nested aggregation. Measures of type: number or type: yesno act as placeholders so other measures or combinations of measures can be referenced within them.

For example, measures of type: number are used to perform calculations between measures and take any valid SQL expression that results in a number or an integer.

The example below uses a type: number to calculate the percentage of all orders that are cancelled:

measure: order_count { # Base measure #1
    type: count
    sql: ${order_id} ;;
}

measure: cancelled_orders { # Base measure #2
    type: count
    filters: [status: "Cancelled"]
}

measure: percent_cancelled_orders { # New measure
    type: number
    sql: (1.0*${cancelled_orders})/ISNULL(${order_count},0) ;;
}

Using a derived table for double or nested aggregations

But what if a nested aggregation is necessary for performing an analysis? For example, what if you need to know the average amount that customers spend in their customer lifetime ("average customer lifetime value")? This requires two levels — a doubling or nesting — of aggregations, including:

1. A sum of sales, grouped by customer

2. An average of that sum

To achieve this with LookML, you might try:

measure: total_revenue {
    type: sum
    sql: ${sale_price} ;;
}

measure: avg_customer_lifetime_value {
    type: average
    sql: ${total_revenue} ;;
}

However, this will trigger the error because the avg_customer_lifetime_value measure is performing an aggregation on the total_revenue measure, which is already an aggregation. As previously discussed, most SQL dialects will trigger an error when double or nested aggregates are used in a query.

To achieve an average of the total_revenue sum in SQL, a subquery such as the following is needed:

  SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) AS s

The equivalent solution in Looker is to create a derived table to "flatten" the total_lifetime_value measure into a field that can be aggregated — in Looker, this is called dimensionalizing a measure. With a derived table, the total_lifetime_value measure becomes a dimension. You can then create a measure of type: average that references the customer_lifetime_value dimension:

view: customer_facts {
    derived_table: {
        sql:
        SELECT
            user_id,
            COALESCE(SUM(sale_price), 0) AS customer_lifetime_value
        FROM orders
        GROUP BY user_id;;
    }

    dimension: customer_lifetime_value {
        type: number
        sql: ${TABLE}."customer_lifetime_value" ;;
    }

    measure: average_customer_lifetime_value {
        type: average
        sql: ${customer_lifetime_value} ;;
    }
}

Once the customer_facts derived table is joined into an Explore, the average_customer_lifetime_value measure can be used to perform the desired analysis in an Explore without triggering any error.