How to create dimension that can filter date to show just the most recently uploaded data?

[ihayes]

I am working with an underlying database where there is  1, 2 or 3 day lag with importing data

I want to build a report that shows data for the most recently loaded day.     For example, if the most recently loaded data was loaded on 15 August 2021, and the system date is  20th August 2021, then the date filter should  find and report on data just for 15 August 2021.

Regrettably, the Looker date filter does not support a ‘MAX(date)’ filter type.

To compensate, I’ve been trying to add a dimension to simulate this with a ‘Most recent’ dimension.(see the LookML below)

My test has the ‘most recent’ dimension set as a filter, with value set to ‘Yes’, but this fails with the error...

  Query execution failed: - Aggregate function MAX not allowed in WHERE clause at [12:31]

Any suggestions?

dimension_group: event {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.eventDate ;;

  }
  
  dimension: most_recent {
    type: yesno
    label: "Most recent"
    sql: DATE_DIFF( ${TABLE}.eventDate, MAX( ${TABLE}.eventDate), DAY) = 0 ;;
  }
  

[ihayes]

Solved it…

Change the view to include a new derived field like…

select *, max(eventDate) over () as maxEventDate from …

Then add two new dimensions to the view file

  dimension: max_event_date {
    type: date
    hidden: yes
    sql:  ${TABLE}.maxEventDate;;
  }

  dimension: most_recent {
    group_label: "Event Date"
    type: yesno
    label: "Most recent"
    sql: DATE_DIFF(${TABLE}.eventDate,${TABLE}.maxEventDate, DAY )  = 0 ;;
  }