Dynamic expression_custom_filter in derived table explore_source

[Rub_moy]

Hi everyone, 

I'd like to know what options do I have to add dynamism to an expression_custom_filter.
Lets say I have two date fields ${orders.sales_date} and ${orders.delivery_date} and I create a derived table:
explore_source: test{ 
column: client{ field: test.client}
column: address { field: orders.address }
column: address { field: orders.address }
column: delivery_date{ field: orders.delivery_date}
filters: [orders.sales_date: "after 2020-01-01"]
}
This works fine, but I'd like the filter to be dynamic. I've tried expression_custom_filter using {% parameter %} but it doesn't seem to accept the use of liquid.
Does anyone have any idea how can I make a expression_custom_filter  dyanmic?

Thanks in advance

[rallaking · Accepted solution]

Hi, if I'm reading your question properly, it sounds like you want to have a date filter that changes the derived table using explore_source that is able to be changed (I'm assuming by users? ). From what I see in the available documentation, it's not possible to add a Liquid parameter when creating from an Explore. LookML allows Liquid in action, description, html, link, some labels, and parameters that begin with sql, such as sql, sql_on, sql_always, sql_where. The explore_source uses expression_custom_filter, which isn't on the list. The syntax for parameters turns red if I try to type it in this section - the ;; made me think it could work, but it doesn't.  https://cloud.google.com/looker/docs/liquid-variable-reference 

Based on that, if the derived table is added using a SQL statement then you can add a parameter (date_select) and then use it in the WHERE clause of your SQL statement to create your derived table. The date_select parameter would be added to the Look/Dashboard filters and would allow the users to input their own dates.

view: order_test {
  derived_table: {
    sql: SELECT
        client as client,
        address as address ,
        delivery_date as delivery_date,
      FROM orders 
            WHERE
         sales_date >= DATETIME({% parameter date_select %})
      ;;
  }
  
  parameter: date_select {
    description: "Choose any date"
    group_label: "Parameters"
    group_item_label: "Date Select"
    label: "Date Select"
    type: date_time
  }

Note that dates can be tricky - my test worked only when I used the DATETIME function to convert the date from the parameter date_select. Depending on your database and column type, you may be able to omit or may need to use a different function. 

If the dynamic changes are not to be made by users but something you'd maintain, then you could set a Constant in the Manifest to define the date to use.  https://cloud.google.com/looker/docs/reference/param-manifest-constant?version=24.20&lookml=new 

Constants are strings so you will have to include additional quotation marks, I use double quotes around '2024-01-01' so that it inserts '2024-01-01'  into the SQL code.

constant: default_date {
  value: "'2024-01-01'"
}

Again this would have to be in a derived table, not using explore_source as far as I can tell (typing @{ } in either filter option turned red which is a bad sign). Unless you want the filter to apply to the original explore, in which case it could be added in sql_always_where.

explore: orders{
  from: orders
  sql_always_where: ${sales_date_date} >= @{default_date}  ;;
}

[Rub_moy]

Thanks @rallaking for your explanation! I will try to find a workaround to get the result I wanted. I do have other scenarios where I will definitely use the example you proposed.
Thanks again!