Create measure from dimension_group

Looker Q&A Looker
1

Is it possible to create a measure based off a dimension_group (e.g. duration)?

e.g.

dimension_group: MyDuration {
  type: duration
  sql: ${TABLE}.”${DURATION_COLUMN}” ;;
  ...
}

measure: m_MyDuration {
  type: sum
  sql: ${MyDuration} ;;
  ...
}
2

Hi @PaulM1 ,

Yes, this is possible. However, in a duration dimension_group, you first need to specify valid intervals to group by (e.g. day, week, month). You would then need to prefix the dimension name with this interval name when referencing it in the definition of the measure, for example:

dimension_group: since_order {

type: duration

intervals: [hour, day, week, month, quarter, year]

sql_start: ${order_date_raw} ;;

sql_end: CURRENT_TIMESTAMP();;

}

measure: average_days_since_order {

type: average

sql: ${days_since_order} ;;

}

measure: average_weeks_since_order {

type: average

sql: ${weeks_since_order} ;;

}

Please let me know if you have any questions on this.

Best wishes,

Shaan