Dynamically filtering a case statement

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

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.

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!

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?

1 Like

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,

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!

Hi Drake,

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

CloudApp

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.

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