How to filter a measure with HLL Sketch fields

[MariaALoeber]

Some customers create fields in BigQuery with HLL (HyperLogLog functions), which create a sketch of a count or value, that will be confirmed during post processing. This post-processing in Looker involves performing an aggregation over the field with a format like: HLL_COUNT.MERGE().

This left us with a challenge, since Looker does not perform the aggregation of an aggregation, which is what we get with this measure definition:

measure: hll_based_measure {
  type: sum    (Aggregation 2)
  sql: HLL_COUNT.MERGE( ${view_name.hll_field});;   (Aggregation 1)
  filters: [filter_field: "Yes"]
}

But, if placing "type: number" to the measure, the filter cannot be applied, which generates another issue. 

Meanwhile, we solved this issue by leaving the measure as "type:number" and replacing the filter by a CASE WHEN clause in the "sql" parameter.

measure: hll_based_measure {
  type: number
  sql: HLL_COUNT.MERGE(CASE WHEN ${filter_field_yesno} THEN  ${view_name.hll_field} ELSE NULL END);;
}

After testing, this brings out the correct numbers, but might face issues when drilling in/out in the measure that is important to take int account. Otherwise, is a solution that might be helpful whan faced with HLL Sketch metrics.