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