Date Parameter In a LookML View

  • 6 August 2021
  • 2 replies

I have a LookML that is powered by a big query view where the dataset that I am pulling from is partitioned on date.


Currently I have the view hardcoded to pull everything from 2017 onward, as shown below:


  date >= '2017-01-01' 


The problem is that this is a 6.9 TB query and takes about 15 minutes to run in the Looker Explorer.


Is it possible to change the date partition I have in the view to a parameter depending on the date range that is selected in the explorer?


For example, say the current date is 8/6/2021: 


I want to pull the last 7 days in the explorer -


I would like the SQL code in the lookml view to update to this:

WHERE date >= ‘2021-07-31’


This would minimize the size and time of the pull drastically.












2 replies

Hi, I’m still new to Looker, but you should be able to do this when you set up the Explore in your model using sql_always_where if you are using a native derived table.


explore: mydataset {
  label: "Last 7 Days"
  sql_always_where: (((  ) >= ((DATE_ADD(CURRENT_DATE(), INTERVAL -6 DAY))) AND (  ) < ((DATE_ADD(DATE_ADD(CURRENT_DATE(), INTERVAL -6 DAY), INTERVAL 7 DAY)))));;


As a tip, I suggest adding the filter onto the Explore and then from the black Data bar, select SQL (defaults on Results) and copy/paste it into the sql_always_where because there are a lot of parentheses going on.

Hi, I would suggest you use a templated filter if you need to use a derived table, something like:

derived_table: {
WHERE {% condition date %} date {% endcondition %} ;;

filter: date {
type: date

Or similarly using date_start and date_end filters.

Maybe a simpler way would be to create a view directly from your table:

view: my_view {

sql_table_name: project.dataset.mydataset ;;



And if you need to have the date to always be >= 2017-01-01, you can set this inside the explore:

explore: my_view {
sql_always_where: date >= "2017-01-01" ;;

This will further help reduce query costs by not needing to select * for every query.