Having issues to pass filter on partition filter as argument to derived sql in LookML

[biswarup83]

Hi,

I have a hive table with datestamp as partition in 'YYYYMMDD' format. I am trying to design a view with a derived sql. 
User should be able to filter by Year (e.g. 2020,2021,2022 .. ) and month in mmm (e.g. Jan, Feb etc) in the explore and be able to generate a report 
Based on the above selection the derived SQL 's start_date (datestamp >= ''YYYYMMDD') and end_date (datestamp <= 'YYYYMMDD') should be substituted.

I am tryin the following:

view: my_facts {

    derived_table: {
      sql:
        Select datestamp,dim1,dim2, sum(measure1) as measure1
            from myDB.table1 where is_fraud = 0  AND
             {% condition start_date_filter %} table1.datestamp {% endcondition %} AND 
              {% condition end_date_filter %} table1.datestamp {% endcondition %} AND 
            {% condition dim1  %} table1.dim1 {% endcondition %}
        group by datestamp,dim1,dim2
        ;;
    }

    filter: dim1 {
      type: string
    }

  filter: start_date_filter {
    type: string
    suggest_dimension: start_date
  }

  filter: end_date_filter {
    type: string
    suggest_dimension: end_date
  }


  parameter: year_to_select{
    type: string
    allowed_value: {value: "2021"}
    allowed_value: {value: "2022"}
    allowed_value: {value: "2023"}
    default_value: "2021"

  }
  parameter: month_to_select{
    type: string
    allowed_value: {value: "Jan"}
    allowed_value: {value: "Feb"}
    allowed_value: {value: "Mar"}
    allowed_value: {value: "Apr"}
    allowed_value: {value: "May"}
    allowed_value: {value: "Jun"}   
    allowed_value: {value: "Jul"}
    allowed_value: {value: "Aug"}
    allowed_value: {value: "Sep"}    
    allowed_value: {value: "Oct"}
    allowed_value: {value: "Nov"}
    allowed_value: {value: "Dec"}    

  }

  dimension: start_date {
    sql: CASE
        WHEN {% parameter month_to_select %} = "Jan" THEN CONCAT({% parameter year_to_select %},'0101')
        WHEN {% parameter month_to_select %} = "Feb" THEN CONCAT({% parameter year_to_select %},'0201')        
        WHEN {% parameter month_to_select %} = "Mar" THEN CONCAT({% parameter year_to_select %},'0301')       
        WHEN {% parameter month_to_select %} = "Apr" THEN CONCAT({% parameter year_to_select %},'0401')
        WHEN {% parameter month_to_select %} = "May" THEN CONCAT({% parameter year_to_select %},'0501')
        WHEN {% parameter month_to_select %} = "Jun" THEN CONCAT({% parameter year_to_select %},'0601')        
        WHEN {% parameter month_to_select %} = "Jul" THEN CONCAT({% parameter year_to_select %},'0701')        
        WHEN {% parameter month_to_select %} = "Aug" THEN CONCAT({% parameter year_to_select %},'0801')
        WHEN {% parameter month_to_select %} = "Sep" THEN CONCAT({% parameter year_to_select %},'0901')
        WHEN {% parameter month_to_select %} = "Oct" THEN CONCAT({% parameter year_to_select %},'1001')        
        WHEN {% parameter month_to_select %} = "Nov" THEN CONCAT({% parameter year_to_select %},'1101')        
        WHEN {% parameter month_to_select %} = "Dec" THEN CONCAT({% parameter year_to_select %},'1201')
        ELSE '20210101'
        END ;;
        label_from_parameter: month_to_select
  }

  dimension: end_date {
    sql: CASE
        WHEN {% parameter month_to_select %} = "Jan" THEN CONCAT({% parameter year_to_select %},'0131')
        WHEN {% parameter month_to_select %} = "Feb" THEN CONCAT({% parameter year_to_select %},'0229')        
        WHEN {% parameter month_to_select %} = "Mar" THEN CONCAT({% parameter year_to_select %},'0331')       
        WHEN {% parameter month_to_select %} = "Apr" THEN CONCAT({% parameter year_to_select %},'0430')
        WHEN {% parameter month_to_select %} = "May" THEN CONCAT({% parameter year_to_select %},'0531')
        WHEN {% parameter month_to_select %} = "Jun" THEN CONCAT({% parameter year_to_select %},'0630')        
        WHEN {% parameter month_to_select %} = "Jul" THEN CONCAT({% parameter year_to_select %},'0731')        
        WHEN {% parameter month_to_select %} = "Aug" THEN CONCAT({% parameter year_to_select %},'0831')
        WHEN {% parameter month_to_select %} = "Sep" THEN CONCAT({% parameter year_to_select %},'0930')
        WHEN {% parameter month_to_select %} = "Oct" THEN CONCAT({% parameter year_to_select %},'1031')        
        WHEN {% parameter month_to_select %} = "Nov" THEN CONCAT({% parameter year_to_select %},'1130')        
        WHEN {% parameter month_to_select %} = "Dec" THEN CONCAT({% parameter year_to_select %},'1231')
        ELSE '20210101'
        END ;;
    label_from_parameter: month_to_select
  }

  dimension: datestamp {
    type: string
    sql: ${TABLE}.datestamp ;;
  }

    dimension: dim1 {
      type: string
      sql: ${TABLE}.dim1 ;;
    }
    dimension: dim2 {
      type: number
      sql: ${TABLE}.dim2 ;;
    }
    measure: measure1 {
      type: sum
      sql: ${TABLE}.measure1 ;;
    }
}

In the explore even though I am successfully able to bring the start_date and end_date as user selects year and month and runs the query, but I am unable to use the values to filter the table1 with datestamp >= $start_date and datestamp <= $end_date. The SQL becomes as following with 1=1:

SELECT
	my_facts.dim2  AS my_facts_dim2,
	CASE
        WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
        WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
        WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
        WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
        WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
        WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
        WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
        WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
        WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
        WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
        WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
        WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
        ELSE '20210101'
        END  AS my_facts_start_date,
	COALESCE(SUM(my_facts.measure1 ), 0) AS my_facts_measure1
FROM (Select datestamp,dim1,dim2, sum(measure1) as measure1
            from tqspace.tq_ybn_click where is_fraud = 0  AND
             1=1 -- no filter on 'my_facts.start_date_filter'
 AND
              1=1 -- no filter on 'my_facts.end_date_filter'
 AND
            (tq_ybn_click.dim1 = '5005950')
        group by datestamp,dim1,dim2
        ) AS my_facts

GROUP BY my_facts.dim2 ,CASE
        WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
        WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
        WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
        WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
        WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
        WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
        WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
        WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
        WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
        WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
        WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
        WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
        ELSE '20210101'
        END
ORDER BY my_facts_dim2
LIMIT 500

Where I want instead is:
 

SELECT
	dim2,
	CASE
        WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
        WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
        WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
        WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
        WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
        WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
        WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
        WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
        WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
        WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
        WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
        WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
        ELSE '20210101'
        END  AS my_facts_start_date,
	COALESCE(SUM(my_facts.measure1 ), 0) AS my_facts_measure1
FROM (Select datestamp,dim1,dim2, sum(measure1) as measure1
            from nyDB.table1 where 
             datestamp >= CASE
        WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
        WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
        WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
        WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
        WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
        WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
        WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
        WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
        WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
        WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
        WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
        WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
        ELSE '20210101'
        END 
        AND datestamp <= CASE
        WHEN 'Jul' = "Jan" THEN CONCAT('2021','0131')
        WHEN 'Jul' = "Feb" THEN CONCAT('2021','0229')
        WHEN 'Jul' = "Mar" THEN CONCAT('2021','0331')
        WHEN 'Jul' = "Apr" THEN CONCAT('2021','0430')
        WHEN 'Jul' = "May" THEN CONCAT('2021','0531')
        WHEN 'Jul' = "Jun" THEN CONCAT('2021','0630')
        WHEN 'Jul' = "Jul" THEN CONCAT('2021','0731')
        WHEN 'Jul' = "Aug" THEN CONCAT('2021','0831')
        WHEN 'Jul' = "Sep" THEN CONCAT('2021','0930')
        WHEN 'Jul' = "Oct" THEN CONCAT('2021','1031')
        WHEN 'Jul' = "Nov" THEN CONCAT('2021','1130')
        WHEN 'Jul' = "Dec" THEN CONCAT('2021','1231')
        ELSE '20210101'
        END
 AND
 AND
            (table1.dim1 = '5005950')
        group by datestamp,dim1,dim2
        ) AS my_facts
GROUP BY A.dim2 ,CASE
        WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
        WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
        WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
        WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
        WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
        WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
        WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
        WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
        WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
        WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
        WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
        WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
        ELSE '20210101'
        END
ORDER BY my_facts_dim2
LIMIT 500

Any help will be great. 

Thanks