Last Tested: March 21, 2021
Looker's min
and max
types only allows for numerical values. Everything else will show a null.
Make a measure of type "date" and apply the "max" aggregate in the SQL. This Help Center article article provides the following example on how we can implement this.
## Suppose we have a type: time dimension group, called updated defined as:
dimension_group: updated {
type: time
timeframes: [time, date, week, month, raw]
sql: ${TABLE}.updated_at ;;
}
## We can create a measure to capture the maximum date of this dimension group as follows:
measure: last_updated_date {
type: date
sql: MAX(${updated_raw}) ;;
convert_tz: no
}
This content is subject to limited support.
Hello Sam, how are you? Im having the same problem. Did you find any solution? https://help.looker.com/hc/en-us/articles/360023425454-Max-or-Min-Date-Measure in this link they are saying to use the MAX and MIN function only in the “sql:” part of the lookerML. But the problems is that by this way the measure type became Non-aggregate type. By this, I cant aply filter to the measure. Looker is awful, stupid softerware. Wish my company shifts to power bi.
Hey @brunoachoa ,
It’s true that you can’t add filters to non-aggregate measure types. You have to write in the filters manually in a CASE WHEN. So, for example, if you wanted to add the filter “filters: [status: "complete"] to my example measure above, then you’d write a measure like this:
measure: last_updated_date {
type: date
sql: MAX(CASE WHEN ${status} = ‘complete’ THEN ${updated_raw} END) ;;
convert_tz: no
}
Hope this helps!
Hey Sam! Thank you for your time. I can aply filter in the sql dimension of the measure, but I cant aply this filters in the looker dashboard. As I would need to aply a lot of diferents filters and create more than one visualization with the measure, unfortunelly this will not be usefull. Example: I need the minimal date for at least 10 diferents groups, so I would need to create 10 diferents measures, what is really unefficient.
Well… I think this can be solved with a complicated Liquid workaround.
First, you’d create a placeholder filter field, which your users would interact with on the dashboard.
filter: group_chooser {
type: string
suggestions: ["group_a", "group_b", ... ]
}
Then, you’d use liquid to insert the value that the user enters into that filter into your measure.
measure: last_updated_date {
type: date
sql: MIN(CASE WHEN ${group} = {{ filters['view_name.group_chooser'] | sql_quote }} THEN ${updated_raw} END) ;;
convert_tz: no
}