I have gone through
https://community.looker.com/technical-tips-tricks-1021/how-to-make-a-weighted-average-30239but 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?
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:
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