Max or Min Date Measure


(Anika Kuesters Smith) #1

Suppose you had a time dimension group, updated:

 dimension_group: updated {
  type: time
  timeframes: [time, date, week, month, raw]
  sql: ${TABLE}.updated_at ;;
}

It might be useful to compute the maximum time for something you are grouping by. This can be done with:

 measure: last_updated_date {
  type: date
  sql: MAX(${updated_raw}) ;;
  convert_tz: no
}

Note that we’re applying the MAX in the sql of the measure, and not using type: max. We’re also applying convert_tz: false. This is because the timezone conversion has already occurred in dimension_group: updated and we don’t want a double conversion in the measure.

Also, the returned value from ${updated_date} is a string. So we use ${updated_raw} to use the actual date value instead.

Max and Min Measures for a Datetime Column

Computing the maximum for a datetime type column is a little different. In this case, you want to create a measure without declaring the type, like this:

 measure: last_updated_datetime {
  sql: MAX(${TABLE}.datetime_string_field) ;;
}

Dimension, return the latest date
Reporting first instance of tag change
Looker Reporting Query Help Needed
(Alison) #2

Anika, There is no ‘convert_tz:’ in the dimension in your example but you seem to be saying that it is doing a conversion on the dimension. What timezone conversion is Looker doing by default? Specifically, what time zone would it assume and what time zone would it convert to?


(Anika Kuesters Smith) #3

Hi Alison! Unless you specify that Looker should not convert timezones, it will convert all time fields based on the database / query TZ settings on the connection (in the admin section). If you have database and query time set the same or don’t fill in these fields, there shouldn’t be any timezone conversion at all.


#4

Two questions.

First, you use type : date above, but it’s not listed at http://www.looker.com/docs/reference/field-reference/measure-type-reference. What gives?

Second, I’m trying to figure out how to take a max or min grouping by another column somecolumn. It looks like it should be something like

  - measure: last_updated_date
    type : string # or: date
    sql: select max(${updated_date}) from ${TABLE} somealias where somealias.${somecolumn}=${somecolumn}

Does that work? Or, how can one do it?


(Zachary Michel) #5

Hey Michael,

There is no particular reason that type: date is not on that document. We will add it.

In terms of grouping a max by somecolumn you’ve almost got it. One thing to note, is that I would use the raw columns when performing this calculation.

- measure: last_updated_date
  sql: |
    (SELECT MAX(your_raw_date_column) 
     FROM table_name somealias
     WHERE somealias.somecolumn = somecolumn)

#6

Many thanks!


(Robert Ellman) #7

I found this article when trying to debug why the min and max date measures that I created using what seemed obvious

  - measure: latest_activity
    type: max
    sql: ${active_on_day}

The weird thing about this was not that it didn’t work, but that it worked and then did not display the results in the Explore view.

When I looked at the data in the Explore view, I see empty date columns

But if I open the SQL tab, and say “Open in SQL Runner” the SQL was perfectly valid and produced the results I was expecting.

Does this need to be a feature request to support min/max measures on dates, or at least to display the results of the SQL query rather than a blank in the Explore view?


(Alison) #8

Robert, do the min or max on the raw field instead of the Looker interpreted field. The Looker interpreted field might be using string casting for formatting based on your SQL dialect.


(Robert Ellman) #9

Alison,

I tried your suggestion by adding the following to my LookML

  - measure: first_activity
    type: date 
    sql: min(${active_on_date})
    convert_tz: false
  
  - measure: first_activity_via_max
    type: max
    sql: ${TABLE}.active_on_day

I think that’s what you meant by using the raw field, the explore view still does not show the value for “max”

while the SQL again seemed reasonable and the SQL Runner showed the results I expected.

SELECT 
	activity_beyond_24.mesh_id AS "activity_beyond_24.mesh_id",
	DATE(min(DATE(activity_beyond_24.active_on_day))) AS "activity_beyond_24.first_activity",
	MAX(activity_beyond_24.active_on_day) AS "activity_beyond_24.first_activity_via_max"
FROM looker_scratch.LKR$VCXDB5RMJWEMGPBYX3_activity_beyond_24 AS activity_beyond_24
LEFT JOIN warehouse.user_account_map AS user_account_map ON activity_beyond_24.mesh_id = user_account_map.mesh_id

I don’t think this is a SQL problem, but rather something in Looker deciding that it can’t/won’t display the value in the Explore view even though the data is returned from the database, possibly because of the Looker metadata around that field, and maybe not supported on all platforms, but where it is supported it seems reasonable to display the returned results.

(As to the dialect, we’re using Redshift)

PS - Sorry the example posted confused min and max, but the behavior is the same


#10

Hey Robert - our min/max types currently only work with integer and number types. Basically, the SQL will return the values correctly, but there is no way for Looker to know what type that field should be. We are working on changing this in the future, but this is the current functionality.

In order to take a max of the date, you will want to calculate the MAX within the sql parameter of your measure. This will look something like:

- measure: last_updated_date
  type: date
  sql: MAX(${updated_date})

Note how the MAX() function is being used, rather than type: max in the LookML. This will let Looker know that this field returns a type date, but you are still performing a MAX() on the field. Depending on how timezones are being applied in your instance, you may also want to add:

  convert_tz: false

to the field, so the timezone conversion isn’t applied twice.


(Rick Saporta) #11

My issue and thoughts are the same as @Robert_Ellman. I had used type:max, type:min etc. The sql ran perfectly, it simply was not displayed.

I used the work around described (thanks for that). It would be great if at some later point the intuitive way of creating these measures could be added


(Carter Moar) #12

Really cool pattern! One thing to keep in mind, not all SQL dialects support this kind of a query. BigQuery, for example, does not support subselects in the select clause. In that case, joining in a derived table might be the best call.


(Tom Zidar) #13

I’m trying to create a max(date) variable and use it as a filter for another column week. However, I cannot match a measure with a dimension in Looker’s filter…

Here’s how I declared the max date variable (neither measure or dimension work)

  • measure: last_updated_datetime
    sql: MAX(${TABLE}.datetime_string_field)

(Sam Wilson) #14

This is a bug and it’s over 1.5 years old. Any plans to ever fix this?


(Conor Flanagan) #15

Hey guys, I have a similar dimension I am trying to build. I basically want to return the most recent date of a date field for a particular dimension. It is probably more clear If I just include this images below. I am trying to build the highlighted dimension. Any thoughts on this?


(jeffrey.martinez) #16

Hey @Conor_Flanagan,

This type of grouping may not work using the syntax described above because your highlighted column in the first table is grouped only by Rep, but SQL would automatically group by unique pairs of Rep & Scheduled date.

By the images you shared, I’m assuming that your original table resembles the first two columns of the first image. If that is the case, then we will need to make a derived table to “dimensionalize” the max(scheduled_date) field grouped by Rep only. More detail on this method is found here:

https://discourse.looker.com/t/dimensionalize-a-measure-cohort-tiers-on-a-count/5191

In your case the derived table would only contain two fields - Rep and max(scheduled_date) (AKA Last Meeting Scheduled). Note, since the max() function is within the sql of the derived table, we would NOT need to add it (as seen above) in the sql of the dimension.
This derived table would resemble the results shown in your second image. We then would join that to your original table in your explore, joined on Rep. Voila! You now have your dimension!


(Brayden) #17

If you do this, the measure is returned as a string which means you cannot use the field in table calculations. Instead here’s what I did which I hope helps someone else in a similar position:

 measure: last_updated_datetime {
  type: date_time
  convert_tz: yes
  sql: MAX(${TABLE}.updated_at) ;;
}