Weighted Average in LookML using measures

[Palaklooker]

I have gone through 

https://community.looker.com/technical-tips-tricks-1021/how-to-make-a-weighted-average-30239

 but am facing an error. 

I have a dimension for weight, a dimension for case types and a dimension for case IDs. I want to get the weighted average for cases based on their case types. the formula i need to use is: 

weighted average = ( case_count(for case_type A)* weight% + case count(for case_type B)* weight% + case count(for case_type C)* weight% ) / (total case_count) 

dimension: weight {    # differs depending on case type associated with case
  type: number
  sql: ${TABLE}.weight ;;  
}

dimension: case_type {    # each case type has different weight
  type: string
  sql: ${TABLE}.case_type ;;  
}

dimension: case_count {
  type: count_distinct
  sql: ${TABLE}.case_id ;;
}

dimension: weighted_count {
  type: number
  sql: ${case_count} * ${weight} ;;
}

measure: weighted_average {
  type: number
  sql: sum(${weighted_count})/${request_count} ;;
 }

this gives the error “Aggregate functions cannot be nested” due to sum function in the weighted_average measure. is there a way to fix this?

[Dawid]

First of all, this has to be a measure:

measure: case_count { type: count_distinct sql: ${TABLE}.case_id ;; }

But most of all, I think you can’t do it all in the same query. In normal SQL, I’m presuming you would use CTE or subquery, as you need to have two levels of aggregations:

1. Count cases and then apply weight (that’s what you can do in Looker)
2. Sum the outcome grouped by case type (second level, not possible to be done in one query without window functions)

The only way you can make it work is if you apply the weight on a dimension level to keep only one level of aggregation. Otherwise you’d have to create a derived table to pre-aggregate the data.

I haven’t tested yet so this is just an idea but maybe you can just do this:

measure: weight_m {
  type: sum
  sql: ${weight} ;;
}

measure: weighted_average {
  type: number
  sql: ${weight_m} / ${request_count} ;;
}

This assumes your weight dimension is numerical % like 0.5, 0.21 and so on. Since you apply weight to a count, you can basically say that each case has this value as a weight on raw data level, since for single case:    1 * 0.5 = 0.5

If we have 5 cases of Type A with weight 0.5, you can do count(cases) * weight or just sum(weight), because 5 * 0.5 = 2.5, and (0.5 + 0.5 + 0.5 + 0.5 + 0.5) = 2.5 as well

Then you have two measures Sum(weight) and Count(Requests), which you can easily put in a measure of type: number