Use dimension type: duration to create a filter denominator

[LookerExperts]

Knowledge Drop

Last tested: Nov 29, 2018
 

If we are looking for a way to take the value of a date filter and use it as the denominator for a profit measure to create a daily profit metric.

1. Create a filter only field

2. Create a dimension of type duration and use liquid to extract the `date_start` and `date_end` for the filter

3. Use the duration dimension as the denominator in the measure!

filter: date_filter {

type: date

}

dimension: number_of_days {

type: duration_day

sql_start: {% date_start date_filter %};;

sql_end: {% date_end date_filter %};;

}

measure: daily_profit {

type: number

sql: ${profit}/${number_of_days} ;;

value_format_name: usd_0

}

This content is subject to limited support.