Question

Crazy average value on measure

  • 12 October 2022
  • 2 replies
  • 112 views

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?

 


This topic has been closed for comments

2 replies

Userlevel 2

I’d check your joins to this table in your explore. Is this the primary table in the explore?

Userlevel 6
Badge

Read this: https://cloud.google.com/looker/docs/best-practices/understanding-symmetric-aggregates