Using "date_start" and "date_end" values of a filter in liquid if statement

[gleb_siz]

Hello, everyone!

I’m using templated filters with filter of type “date”. At the same time I need to display the period from filter on tile like “Time period: date_start -  date_end. The question is how to handle cases when user choose period with not static boundaries (like after…, before… etc.). I’m trying use if statement. Seems that looker put NULL into SQL in case when one of the boundaries is not chosen, but what is proper check in if statement for this? or how to  refer to date_start and date_end properly? Btw it doesn’t work with nil, null and “NULL”. My try:

 filter: date_filter{
    type: date
  }

measure: report_timeframe {
    type: string
    sql: {% if date_filter.date_start != nil and date_filter.date_end != nil %}
          TO_CHAR({% date_start date_filter %},'yyyy-mm-dd')||' — '||TO_CHAR({% date_end date_filter %} - INTERVAL '1 day', 'yyyy-mm-dd')
         {% elsif date_filter.date_start != nil and  date_filter.date_end == nil %}
          'from '||TO_CHAR({% date_start date_filter %},'yyyy-mm-dd')
         {% elsif date_filter.date_start == nil and date_filter.date_end != nil %}
          'before '||TO_CHAR({% date_end date_filter %} - INTERVAL '1 day', 'yyyy-mm-dd')
         {%else %}
          'not specified'
         {% endif %}

[AReasonableMan]

I would also like to know the answer to this.  

[robertcarr]

Hello,

Thanks for your question. 

One way of doing this is deciding how to handle these nulls. An example can be seen below when I say if the date_start is null, use today's timestamp. Let me know if this helps.

filter: date_filter {
type: date
}

measure: total_count {
type: count_distinct
sql: CASE WHEN ${created_raw}<IFNULL({% date_start date_filter %},CURRENT_TIMESTAMP()) then ${id} else null end ;;
}