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?
