Question

Dimension, return the latest date

  • 14 February 2018
  • 10 replies
  • 1484 views

Hello Looker community,


I am trying to write a dimension (last meeting date) that will return the latest date from a dimension of dates. It’s for my sales team. I want the dimension to return the date of the last meeting a rep has set. So it would look like this:


Rep Meeting Date Last meeting date

Gary 3/14/17 2/10/18

Gary 5/20/17 2/10/18

Gary 5/27/17 2/10/18

Sean 8/11/17 1/30/18

Gary 2/10/18 2/10/18

Sean 1/30/18 1/30/18


I tried to do this with a max sql operator but couldnt get it to work. Any thoughhts on this?


10 replies

Hi Conor,


Anika wrote a great post on this question here: Max or Min Date Measure


In short, we end up taking the raw timeframe and making a measure of type: date and putting the MAX() in the sql parameter.


Cheers,


Sami

Hi, I’m having the same issue. I read the linked article but I need to use it in a look as a series label for a bar chart. You can’t pivot measures, so I really need it as a dimension.


Anyone know how to work around this?

Userlevel 7
Badge +1

If you need it as a dimension for sure, pretty much your only choice is to either do it in a derived table, or, if it’s just this one field and you just need the overall max, simulate a derived table by defining a dimension with a subquery:


dimension: subquery {
type: number
sql: (SELECT MAX(datefield) FROM table) ;;
}

If you want to not just return the #1 max date from a table, the subquery option won’t work. For example, if you’re doing something like the OP where you want to actually get the max date for each rep, you’d want to add a window function and partition by rep. This’d have to be done in a derived table.


Does that make sense at a high level?

Yes, I got it working with a derived table. Thanks!

Userlevel 3

I’ve got the same issue and I read the linked article, but it doesnt work for me.

If I do either



measure: last_updated_date {

type: date

sql: MAX(${updated_raw}) ;;

convert_tz: no

}



or



measure: last_updated_datetime {

dimension: subquery { type: number sql: (SELECT MAX(datefield) FROM table) ;; }


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


then it calculates the max in the line - so it just gives me the same date back.

and if I do this



dimension: subquery {

type: number

sql: (SELECT MAX(datefield) FROM table) ;;

}



then I get a SQL error.

Userlevel 3

so for example:


image


now the slight difference in days in the last column is because of Looker’s timezone issue…but that is not the big concern here…

Userlevel 7
Badge +1

I might have been a bit remiss in posting this snippet. It definitely works on some SQL dialects, but may not on every dialect. What’s the SQL error you’re getting?


If you can’t run a subquery in a dimension, then I think you’ll have to use a derived table, which is what @Rachel ended up doing anyways. You could also use a table calculation and forget all the complicated SQL if your explore structure allows for it!

Userlevel 5
Badge

@izzy - quick question for you. Would using a subquery like this apply the filters applied and/or in the sql_always_where clause, or would it be treated as a direct query of the underlying table? Just want to understand whether this method could potentially add risk of inadvertently filtering out necessary data. In this case, for example, if only one row has the last_updated_date and it was filtered out in the Explore environment, would the subquery see that row at all?

Userlevel 7
Badge +1

That is a great question! The filters applied in any where clause (filters or sql_always_where included) would not apply to the subquery dimension.


The subquery should always return the same number regardless of filters applied— which raises concerns about data confusion in the opposite direction you mentioned, too, and should be treated with caution. Users generally expect that filters apply to the entire dataset they are returning, and this circumvents that.


If you don’t want that effect, a derived table is the way to go since that would get joined in before the WHERE clause applies


quick test to make sure that you could replicate easily:


f0_ is filtered according to the WHERE clause and is smaller than the result of f1_, the subquery, which is not affected by the user_id > 50000 filter.

This worked for me. 

dimension: last_updated_date_interim {
type: string
hidden: yes
sql: (SELECT MAX(SNAPSHOT_DATE) FROM "my_schema"."my_table_name");;
}

dimension: last_updated_date_filter {
type: yesno
sql: ${last_updated_date_interim}::date = ${snapshot_date};;
}

 

Reply