How do I set a liquid parameter based on a dashboard filter?

[Piotr_Kalachyn_]

Hi all,

I have a LookML dashboard with a filter for two looks

- dashboard: overview
  filters:
    - name: time_spec
      title: Event Time Range
      type: date_filter
      default_value: last 7 days

both elements of the dashboard listen to the filter:

    listen:
      time_spec: view1.modification_date

 and

    listen:
      time_spec: view2.creation_date

now I want to change a certain value in the derived table: sql of view1 based on the values of the filter
 

view: view1{
  derived_table: {
    sql:
      WITH

      days AS (
          SELECT *
          FROM UNNEST(
            GENERATE_DATE_ARRAY (
              DATE(TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY))),
              CURRENT_DATE()
            )
          ) as sample_date
      ),

How do I use a dynamic value instead of the 180? Let’s say the filter is set to last 14 days, then I want the interval clause to be INTERVAL 14 DAY.

[Piotr_Kalachyn_]

When I try to 

DATE(
      TIMESTAMP( COALESCE({% date_start time_spec %}, DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)))
),

and define a Liquid filter of the same name as the dashboard filter:

 filter: time_spec{
    type: date
    default_value: "last 30 days"
 }

the result is the look displaying the last seven days – apparently {% date_start time_spec %} is null.

[Piotr_Kalachyn_]

The reason why my previous attempt did not work was different types of COALESCE arguments. Having checked the generated SQL, I changed to fallback expression (2nd argument) as follows:

              DATE(
                TIMESTAMP(
                  COALESCE(
                    {% date_start time_spec %}
                   , TIMESTAMP_ADD(
                       TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), 
                       INTERVAL -26 DAY
                    )
                  )
                )
              ),

It is probably BigQuery specific, so check what date_start generates in your SQL dialect.

Now the funny thing is, I got “No results” when I skipped the COALESCE wrapper and tried to rely on the filter’s default value.

So my task is solved, and only out of curiosity I wonder why COALESCE (or perhaps some IFNULL) is needed. Any ideas anyone?