Looker is generating a crazy average SQL and I don't understand why.
In my view, I have a number field which records assignment grades for students:
dimension: step_grade {
type: number
sql: ${TABLE}.step_grade ;;
}
This field is correctly populated in BigQuery with numbers only. It's a FLOAT column.
When I query it for average, it returns properly:
So, I created a measure in the same view to bring out the average value for this field:
measure: average_step_grade {
type: average
sql: ${TABLE}.step_grade ;;
value_format: "0.00\%"
}
Sometimes it works well. Other times, it gives me a non-sense result.
I noticed that it generates a crazy query for this field.
(ROUND(COALESCE(CAST( ( SUM(DISTINCT (CAST(ROUND(COALESCE( enrollments.step_grade ,0)*(1/1000*1.0), 9) AS NUMERIC) + (cast(cast(concat('0x', substr(to_hex(md5(CAST( enrollments.enrollment_id AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST( enrollments.enrollment_id AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001 )) - SUM(DISTINCT (cast(cast(concat('0x', substr(to_hex(md5(CAST( enrollments.enrollment_id AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST( enrollments.enrollment_id AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001) ) / (1/1000*1.0) AS FLOAT64), 0), 6) / NULLIF(CAST(COUNT(DISTINCT CASE WHEN enrollments.step_grade IS NOT NULL THEN enrollments.enrollment_id ELSE NULL END) AS FLOAT64), 0.0)) AS enrollments_average_step_grade
In my understanding, it should just be AVG(field_name).
The whole calculation is wrong and I am not sure how to fix this.
Any ideas?
I’d check your joins to this table in your explore. Is this the primary table in the explore?
I'm also having this issue. Average of a binary sometimes correctly equals the rate, 71% or w/e, and other times is 3.2354614402792E+19 or somehow even negative -3.99141920340004E+19