Max or Min Date Measure Showing Nulls

Knowledge Drop

Last Tested: March 21, 2021

Why is this happening?

Looker's min and max types only allows for numerical values. Everything else will show a null.

How do I fix it?

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.                

Comments
brunoachoa
New Member

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.

sam8
Staff

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!

brunoachoa
New Member

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.

sam8
Staff

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
}
Version history
Last update:
‎04-05-2021 09:13 AM
Updated by: