Adding a custom dynamic filter to LookML dervied table

[achuang]

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?