Using Maximum Date in Filter to Filter Results

[Tim_Coultas]

Hi 

I posted this question here: https://www.googlecloudcommunity.com/gc/Modeling/Templated-filter-to-get-the-max-date-in-Looker-Look... but am new to this forum so it's not clear to me if it will be reviewed. 

I believe the question that we are trying to solve is similar to the thread above, but would like clarification about deriving the max value of a date from a filter if possible.

We have a dashboard where our users are able to filter by the date_day field in person_fact. (I have given examples of the views and explore below.) This is an advanced date filter so they are able to filter by lots of different options. We would like to provide a count of person_ids in two ways: 

1) Count all person_ids in the date range selected. This works as is today. If a user selects "In the year 2024" they'll get a count of all person_ids that appear in the person fact table in that year. 

2) Count the person_ids that appear in the person_fact table for the last day selected in the date filter. So for example, if I pick October 2024 in the date filter, I want to get a count of all person_ids in the person_fact table where date_day = '2024-01-31'. 

It appears that the approach above would solve for the second use case, but it's not entirely clear to me how we would obtain the max date selected in the date filter. 

For simplicity, we have two views: person and person_fact.

person_fact

view: person_fact {
sql_table_name:
-- if prod -- xxx
-- if dev -- yyy
;;

dimension: date_day {
type: date
sql: ${TABLE}."DATE_DAY" ;;
}

dimension: person_id {
type: number
sql: ${TABLE}."PERSON_ID" ;;
}

measure: count {
type: count
}
}

person
view: person {

sql_table_name:
-- if prod -- aaa
-- if dev -- bbb
;;

dimension: person_id {
primary_key: yes
type: number
sql: ${TABLE}."PERSON_ID" ;;
}
dimension: sex {
type: string
sql: ${TABLE}."SEX" ;;
}
}

These are surfaced in an explore: 

explore: person {
label: "Persons"
join: person_fact {
view_label: "Person Fact"
relationship: one_to_many
sql_on: ${person.person_id} = ${person_fact.person_id};;
}
}

Thanks in advance for help with this!

Tim 

[GavinW · Accepted solution]

There is a Liquid variable that captures the last date entered in a date range filter - 

{% date_end date_filter_name %}

So you can use this to pick up the value and inject it somewhere else - for example a dimension that gives you the final date in a given month. The code looks something like this, but the code in the sql: parameter for the last_day_of_filtered_month will depend on your SQL dialect:

dimension: filter_date {
  type: date
  sql: ${TABLE}.filter_date ;; # Your date field
}

dimension: last_day_of_filtered_month {
  type: date
  sql: DATE_TRUNC('month', {% date_end filter_date %}) + INTERVAL '1 month' - INTERVAL '1 day' ;;
}

[Tim_Coultas]

Thank you, this was very helpful. Was able to get it working, albeit with some date math, casting, etc etc to work out.