How to calculating the median of a duration?

Looker Q&A Looker
,
1

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”)

2

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.

9 Likes