Date Parameter in Derived Table used in more than one way

[iproz]

Has anyone been able to efficiently parameterize a derived table with a date parameter such that the date is used in two different ways?

For example, given the SQL:

select
  sum(revenue)
from orders
left outer join invoices on orders.id = invoices.order_id
where
  order_date = '2023-01-01'
  and invoice_date >= '2023-01-01'

I know how to parameterize it with a templated filter

{% condition date_filter %} sales.order_date {% endcondition %}
and {% condition date_filter %} sales.invoice_date {% endcondition %}

so that both date criteria would equal the date passed in, but is there a way to get the SQL to use the date passed in for the >= clause on the other date field above?

I am able to do it by instead parameterizing a date_dim table, joining to that, and using the date returned in the two criteria, but then BigQuery isn’t able to take advantage of partitioning.

Any help would be appreciated!

[thinh96-1657593]

Hi, If you can access into LookML. 
 

Let try another way that you create a derived table in another view file. 

Here is the document that you can refer

https://cloud.google.com/looker/docs/reference/param-view-derived-table

[Victor_Lorente · Accepted solution]

where
  order_date = {% date_start date_filter %}
  and invoice_date >= {% date_start date_filter %}

[iproz]

Didn’t know about date_start and date_end. Thank you!