I’m trying to use templated filters to allow the user to select the number weeks they want to see trend on. I came up with a way to do this but it only works if the user selects the filter. Since the templated filter creates 1=1 when no filter is selected, I’m unable to write multiple CASE WHEN scenarios. Are there any other ways to accomplish what I’m trying to do here? I’d like to give the users an option to select up to the last 13 weeks.
Below is the LookML
- filter: number_of_weeks_for_trend
type: int
default_value: 6
suggestions: [1,2,3,4,5,6,7,8,9,10,11,12,13]
- dimension: number_of_weeks_to_trend_key
hidden: true
type: int
sql: |
(CASE WHEN {% condition number_of_weeks_for_trend %} 1 {% endcondition %} THEN 14
WHEN {% condition number_of_weeks_for_trend %} 2 {% endcondition %} THEN 21
WHEN {% condition number_of_weeks_for_trend %} 3 {% endcondition %} THEN 28
WHEN {% condition number_of_weeks_for_trend %} 4 {% endcondition %} THEN 35
WHEN {% condition number_of_weeks_for_trend %} 5 {% endcondition %} THEN 42
WHEN {% condition number_of_weeks_for_trend %} 6 {% endcondition %} THEN 49
WHEN {% condition number_of_weeks_for_trend %} 7 {% endcondition %} THEN 56
WHEN {% condition number_of_weeks_for_trend %} 8 {% endcondition %} THEN 63
WHEN {% condition number_of_weeks_for_trend %} 9 {% endcondition %} THEN 70
WHEN {% condition number_of_weeks_for_trend %} 10 {% endcondition %} THEN 77
WHEN {% condition number_of_weeks_for_trend %} 11 {% endcondition %} THEN 84
WHEN {% condition number_of_weeks_for_trend %} 12 {% endcondition %} THEN 91
WHEN {% condition number_of_weeks_for_trend %} 13 {% endcondition %} THEN 98
ELSE 49
END)
- measure: product_count_trend
hidden: true
label: 'Product Count Trend'
description: 'USE WITH Number of Weeks to Trend FILTER'
type: count_distinct
sql: |
(CASE WHEN ${retail_calendar.week_end_dt} <= (current_date-7) AND ${retail_calendar.week_end_dt} > (current_date-${number_of_weeks_to_trend_key})
THEN (${product_name} || ${retail_calendar.week_end_dt}) ELSE NULL END)
When filter is not selected the it results in the below SQL which obviously won’t work. Looking for a way to use templated filters as a parameter of sorts… any and all ideas are appreciated!
(COUNT(DISTINCT (CASE WHEN (DATE(retail_calendar.week_end_dt)) <= (current_date-7) AND (DATE(retail_calendar.week_end_dt)) > (current_date-(CASE WHEN ( 1 = 6) THEN 14
WHEN ( 1 = 1) THEN 21
WHEN ( 1 = 1) THEN 28
WHEN ( 1 = 1) THEN 35
WHEN ( 1 = 1) THEN 42
WHEN ( 1 = 1) THEN 49
WHEN ( 1 = 1) THEN 56
WHEN ( 1 = 1) THEN 63
WHEN ( 1 = 1) THEN 70
WHEN ( 1 = 1) THEN 77
WHEN ( 1 = 1) THEN 84
WHEN ( 1 = 1) THEN 91
WHEN ( 1 = 1) THEN 98
ELSE 49
END))
THEN (merch_sku_daily.product_name || (DATE(retail_calendar.week_end_dt))) ELSE NULL END)
