How do I fix the error "select list expression [...] references which is neither grouped nor aggregated" in LookML?

Looker Q&A Looker
1

I get the error "

invalidQuery: SELECT list expression references tb_ind_performance_360.mes which is neither grouped nor aggregated at [2125:17]" when running the following measure in LookML:

measure: insta_prazo_mes_segui{
type: number
sql: case
when (${valor_ms_date} >= DATE(“2022-02-01”)) AND
(${valor_ms_date} <= current_date()) then ${gross}
END;;
}

How can I correct the measure so that it works?

2

When you create a measure in LookML, there are two main categories of measure types:

  1. those that apply the SQL aggregate function for you - e.g. sum, count, average
  2. those that are more flexible but require you to ensure you apply the aggregates yourself - e.g number, string

In this case, it looks like you’re trying to do some sort of calculation. You should ensure that you either apply SQL aggregate functions in your measure’s SQL, or reference fields that are themselves measures which already have aggregates applied

3

The aggregation measure I have is this:

measure: gross{
label: “GROSS”
type: sum
sql: ${qtde} ;;
filters: [indicador: “GROSS”, descricao: “Gross”, complemento: “OBRA CANCELADA, OBRA CONCLUÍDA, OBRA EM ANDAMENTO, SEM OBRA”]
#value_format: “[>=1000000]0.0," M";[>=1000]0.0," K";0”
}

however, the error message I receive says that the date dimension is not aggregated, but I don’t know how to aggregate it in the lookml