Question

Adding a custom dynamic filter to LookML dervied table

  • 20 January 2023
  • 0 replies
  • 16 views

I need to add a dynamic filter to a view so I would only have to change the filter when changing the parameters. In the derived table, the filtering looked like this:

 

WHERE
((client_status = 'Client' and date_trunc('month',APPLICATION_APPROVAL_DATE) <= '2022-12-01') OR
(client_status = 'Former Client' and date_trunc('month',churn_date)>'2022-12-01' and date_trunc('month',APPLICATION_APPROVAL_DATE) <= '2022-12-01'))

The goal is the to make the value being compared to APPLICATION_APPROVAL_DATE dynamic so I went ahead and created this LookML:

 

  filter: custom_date_filter {
type: string
}

dimension: month_flag {
type: yesno
sql:
CASE
WHEN (${client_status} = "Client" and ${application_approval_date} <= ${custom_date_filter}) OR
(${client_status} = "Former Client" and ${churn_date} > ${custom_date_filter} and ${application_approval_date} <= ${custom_date_filter}) THEN TRUE
ELSE FALSE
END;;
}

However, when the filter is used in the explore, the custom_date_filter does not populate, regardless of what I put in it. This leads to a SQL query that cannot be run due to missing values.

 

The end goal is to have the month_flag dimension come up as yes or no in which that can be filtered. 

 

Can anyone help with this?


0 replies

Be the first to reply!

Reply