Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Dynamically filtering a case statement

  • 26 August 2019
  • 8 replies
  • 2104 views
Florian_Tourneur

Hi there,


I have been struggling trying to create a dynamic filter based on a case statement in looker. I have a SQL query as follow:


CASE WHEN contract_end >= DATEADD(month,0, GETDATE()) THEN ‘Not Ended’

ELSE ‘Ended’ END AS “Contract Status”


I’m trying to create a filter that would allow me to dynamically look at last month or last year contracts that are “Not Ended”. This is easy to do in SQL but doesn’t seems to be an easy task in Looker.


Hopefully someone could help me with this,


Cheers,

Florian

8 replies

izzy
Userlevel 7
Badge +1

If you define that case statement as a dimension in Looker, then you’ll be able to filter on it like you would any other field—


dimension: contract_status {

type: string

sql: CASE WHEN contract_end >= DATEADD(month,0, GETDATE()) THEN ‘Not Ended’

ELSE ‘Ended’ END;;

}

Then, you’d be able to add a filter on contract status and set it to ended/not ended. If you also add another filter on month, you can dynamically mix and match to your heart’s content.

Florian_Tourneur

Hi izzy,


Thanks for the response here!


How should I be go with adding a timeframe dimension with this statement?


Like if I wanted to know what was the past 3 months of ‘Not Ended’ contracts month per month…


Let me know if you have a solution,


Thanks!

D
Rank

Hi Florian!


There’s always a few ways to approach things like this with Looker. For your use case, I’d recommend using a templated filter inside your case statement. This requires creating a custom filter that users will be able to interact with. This filter will then be applied to any objects you’d like – including the case when.


The LookML would look something like this:



filter: custom_date_filter {

type: string

}




dimension: contract_status {

type: string

sql:

CASE

WHEN {% condition custom_date_filter %} ${contract_end} {% endcondition %} THEN ‘Not Ended’

ELSE ‘Ended’

END;;

}



This will result in the user being able to use all of the filter options available in the Explore or Dashboard filter. The filter will then be applied to the dimension ${contract_end} to create a logical statement. This results in a user-friendly experience which dynamically adjusts the CASE WHEN depending on user behavior.


Does that make sense?

Florian_Tourneur

Hi Drake,


Thanks for jumping-in!


I guess my question wasn’t properly explained. I modified my model that now ends with:


WHERE “last contract end date” >= DATEADD(month, -1 , {% date_start date_filter %})


I created the filter, parameter and dimension as below:


filter: date_filter {

type: date_time

convert_tz: no

view_label: “Enter Date”

}


parameter: date_granularity {

type: string

allowed_value: { value: “Month” }

allowed_value: { value: “raw” }

}


dimension: date {

label_from_parameter: date_granularity

sql:

{% if date_granularity._parameter_value == “‘Month’” %}

${date_filter}::VARCHAR

{% else %}

${date_filter}::VARCHAR

{% endif %} ;;

}


It seems like it’s working so far but as you can see on the screenshot below it only returns one row. What I would like is getting each month as 1 row so in this case 9.



CloudApp



Hope it makes sense,


Thanks,

D
Rank

Hi Florian,


I see two issues. The first is that your dimension is not referring to any data from your database in the sql parameter; it’s only referring to the custom filter you’ve created. The second is that you’re not actually doing any transformation to the date.


I think you want it to be like this:



dimension: date {

label_from_parameter: date_granularity

sql:

{% if date_granularity._parameter_value == “‘Month’” %}

date_trunc(‘month’, ${contract_end})::VARCHAR

{% else %}

${contract_end}::VARCHAR

{% endif %} ;;

}



With this, if month is chosen as the value for the parameter, it will then transform the ${contract_end} into a month. You can then expand this to include weeks, quarters, years, etc.


Let me know if that works!

Florian_Tourneur

Hi Drake,


I tried your code but keep getting an error message as shown below…



CloudApp


izzy
Userlevel 7
Badge +1

The syntax seems fine in @drake.williams’ example — Are you sure you left just single quotes around the month in date_trunc('month'? That error could come from putting double quotes around it.

Florian_Tourneur

I’ve tried again and it doesn’t seems to be working…

Reply


Terms of Service