Filtering records based on a user specified date that falls between start date and end date fields

[bethanyweisberg]

Without creating a derived table, is there a way to filter records in Lookml based on a user-specified date which falls between a start date field and end date field?

For example, a user chooses the date 4/01/2024, so it would filter records where the start date field is on or before 4/01/2024 and the end date field is on or after 4/01/2024? Any help is much appreciated! 🙂 

[salehaxsid]

Hello,
1. You can try to create a filter that takes date value as input like this:
filter: date {
type: date
}

2. then create a dimension that takes the value of date filter as input and considers it as start date using liquid variable reference:
dimension: filter_start{
type: date
sql: {% date_start date %} ;;
}

3. Once start date dimension is created then create a dimension that adds 1 year interval or you can customize it according to the datepart and interval you require:
dimension: filter_end{
type: date
sql: cast(date_add(cast ({% date_start date %}as date),interval 1 year)as timestamp);;
}

4. Add date filter in explore and provide the suitable date.
5. Add ${orders.order_date}>=${orderdetails.filter_start} AND ${orders.order_date}<${orderdetails.filter_end} this condition to custom expressions within filter pane. This condition will give you data of one year starting from the date you provided in the filter.
6. You may get datatype error when working with timestamp and date, simply cast the type into suitable one.