Calculating the max date in a column

[GenDemo]

Hi All

is there a way to calculate the maximum (last) date in a column?
I was thinking something as simple as " max( {$table.date} ) " but this doesnt work.

[Krishna_Shukla]

Hi @GenDemo , It looks like you forgot to change dimension to measure 🙂, whenever you use functions like max,count,sum etc. then it has to be measure.

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

If you are still getting error or some other case then it would help if you can share us the dimension lookml you written and sql generated behind it.

Reference : https://help.looker.com/hc/en-us/articles/360023425454-Max-or-Min-Date-Measure

[GenDemo]

Hi
Thnx for your reply, however, I mean in a table calculation. So in the LookML in the background, the data field is a dimension_group. But I want to do calculations off the max/latest date in a column in my report.

[Todd_Osborne]

We would have many use cases for calculating the max/latest date using a Table Calculation or Customer Measure/Dimension! Does anyone know how to do this?

[adstott90]

Hi @Todd_Osborne ,

I think @Krishna_Shukla’s answer further up the thread covers what you’re looking for - might be worth taking a look at that and seeing how you go!

Thanks, Adam

[Dawid]

Yes date functions in table calculations are much needed. Sometimes you don’t want to put everything in the lookML

[Quinn_Wildman]

It possible to do this in a calculation. Example:

add_days(max(diff_days(to_date(“1900-01-01”),${my_table.date})),to_date(“1900-01-01”))

This example assumes your earliest date is Jan 1, 1900. If your earliest date is before then, my experimentation says you’ll have to add 1 day as Looker computes the day off by 1 for me.

[GenDemo]

I concur most definately

[GenDemo]

no this doesnt actually work.
This method calculates the max in each line, so it ends us just giving you the same value as the raw number.

[Jimmy_Westman]

Quinns solution works perfectly from what I can tell.

[GenDemo]

Its been a while now, but I think I was referring to Krishna’s reply.
max(${TABLE}.LastReviewedOn);;
does not work.

[acrobat]

Below approach worked for me. With it, I can filter on max date.

 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};;
  }