I’m at a loss as to how to do this as it’s throwing an error in looker …
I have 2 dates were i’m calculating the duration
dimension_group: dwell_time {
type: duration
intervals: [second, minute, hour, day]
sql_start: ${firstfield_date_created_time} ;;
sql_end: ${secondfield_date_created_time};;
}
> Value works fine
If I run the avg dwell
measure: avg_dwell {
type: average
value_format_name: id
sql: ${minutes_opp_addsite} ;;
}
> This works correctly
However when I try and get the median (because avg swings this way too much on high times)
measure: median_dwell {
type: median
value_format_name: id
sql: ${minutes_opp_addsite} ;;
}
> I get this error:
A LookML model issue prevented this query from running.
SQL Dialect "snowflake" does not support Symmetric Aggregates with percentiles, field ignored. (In field "salesforce_account.median_dwell")
As always happens … after I make the post, I found the answer. In case somebody else comes up against this:
https://docs.looker.com/reference/explore-params/symmetric_aggregates
specifically:
If you receive an error similar to SQL dialect doesn't support Symmetric Aggregates with percentiles, field ignored.
, this indicates that your database dialect does not support the percentile_distinct
and median_distinct
measure types. To work around this, change the measure type to type: number
and then specify the aggregate function with sql: median(${dimension})
. This disables symmetric aggregates, however.