Question

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

  • 2 September 2021
  • 2 replies
  • 49 views

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.


2 replies

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.

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? 

Reply