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} ;;
...
}
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