Filter on current week and the previous week using templated filters in LookML

[JVFrancis]

I am trying to create templated filters in LookML to filter the current week and the previous week of data. The current week date range in this scenario would be 02/26/2025 to 03/04/2025  and the previous week date range in this scenario would be 02/19/2025 to 02/25/2025 when the report is run as of today i.e., 03/05/2025 . Is this possible using templated filters in LookML? I would want both the date ranges (both current week & previous week) to change dynamically by default based on which day the report has been run on.

[blue1]

Hi Francis, you can achieve this using the dateadd()/date_add() function and getdate()/now(). Check your SQL dialect to know which one to use.

dimension: previous_week_filter {
  type: yesno
  sql: ${finished_datetime_utc_date} BETWEEN DATEADD(day,-8,getdate()) AND DATEADD(day, -1, getdate()) ;;
}
  
dimension: previous_week_filter {
  type: yesno
  sql: ${finished_datetime_utc_date} BETWEEN DATEADD(day,-15,getdate()) AND DATEADD(day, -8, getdate()) ;;
}

If you want to use this on a dashboard, I recommend combining the logic into one dimension, as it is not possible to create an "OR" relationship between two filters on a dashboard. 

dimension: current_week_or_previous_week_filter {
  type: yesno
  sql: (${finished_datetime_utc_date} BETWEEN DATEADD(day,-8,getdate()) AND DATEADD(day, -1, getdate())) --current_week
       OR (${finished_datetime_utc_date} BETWEEN DATEADD(day,-15,getdate()) AND DATEADD(day, -8, getdate())) --previous_week
      ;;
}

Hope this helps!