Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Filter dependent on other filter

  • 20 November 2019
  • 9 replies
  • 3683 views
moebe
Userlevel 4

hello together

we have usually used two filters.



  1. filter on the evaluation date (we evaluate our portfolio daily)

  2. filter on the delivery date (when do our contracts go into physical or financial delivery)


Now we often have the request to see the song date depending on the valuation date.


So: e.g. valuation date = 15.10.2019.

Then only deliveries larger than 15.10.2019 should be displayed.


Is there a possibility to make the two filters dependent on each other to “be able”, but not to make them dependent to “have to”.

9 replies

moebe
Userlevel 4

solved by custom filter, with great help from support.

for me, thats still a feature request, because this could help in a lot of our cases, where two date filters needs to be synced and the user are not mature enougth to do custom filter

izzy
Userlevel 7
Badge +1

I see the value here for sure. I’m wondering how you’d envision it working in your ideal world.


When I think about it, the difficulty is with the way that we do date filters. They’re super flexible, so you have a wide variety of options and sometimes up to 4 different dropdowns that let you filter on different things. Basically, the input for a date filter is logic:

image
.


That’s in contrast to a simple field filter that actually just takes 1 piece of input and usually has a dropdown list of suggestion options:

image
. In the second filter, it’d be easy to add the value from Date Filter Part 1 as a filter on the query that powers the suggestions (and in fact, we allow this to be done on Dashboards— It’s called faceted filters). What’s not easy is to apply the filter from Date Filter Part 1 to, say, Date Filter Part 2.


Would your ideal state be that the user is then presented with a dropdown list of dates, or a calendar picker or something like that? There are some Date Filter options that might let that work, like in range, or if you select an absolute instead of relative value:

image


But the question of how to update a date filter from another filter, without restricting the types of filtering that can be done, still stands. Maybe the best thing to do is to restrict it to just a range or an absolute date?


Interested to hear what you think! Will definitely pass this along to product (Don’t worry, it’s really our job to figure out how to do it, I’m just curious what you think!).

moebe
Userlevel 4

@izzy


I can show you, how other bi-tools solve this.


Here you can see that I add two elements.

filter_1


Now I decide,that the delivery_date must be >= valuation_date

filter_2


Here you can see the generated SQL.

As this logic is dynamic I can change my Valuationdate to whatever date I want, I will only receive records where the delivery_date >= valuationdate.


filter_3


I think in looker, it could be a possibility to add this functionality to the highlighted section.

This you could expand to nearly all combinations (f.e. One Date to another, or f.e. one measure to another (f.e. one measure must be bigger then another) usw.


filter_4


All that you can do with custom filter, I know.


Hope that helps.


Cheers

Benjamin

izzy
Userlevel 7
Badge +1

Ah okay I get it, I thought you had a bit more complicated requirement in mind. That makes sense, thanks for the visuals! I’ll pass it along 🙂 .

izzy
Userlevel 7
Badge +1

https://github.com/looker/analytics/issues/8462

B
Userlevel 5
Badge

Hey Benjamin,


I don’t have a crystal clear vision of how to do this, but conceptually, if you’re looking for conditional/relational filtering, I think you could accomplish this simply with a user yesno parameter and sql_always_where.


My idea is:



  1. Build a yesno parameter - let’s call it only_see_new_deliveries - with a default_value equal to “no”

  2. Use (or add to) your sql_always_where clause in the affected Explore(s). Using Liquid, you can define that if only_see_new_deliveries = ‘yes’, then restrict the data accordingly. In your example, I suspect it’d look something like:


{% if condition view_name.only_see_new_deliveries._parameter_value == "yes" %} 

 ${TABLE}.delivery_date >= ${TABLE}.valuation_date

{% endif %}

Edit: Two things - one, I’m questioning myself as to whether this should be a filter or a parameter.

Next, it’s worth mentioning that another pattern which can be used as an extension off of this pattern, is date offsetting with an unquoted user parameter and date_add (or your local dialect’s version of it) injected into the SQL comparison with a bit more Liquid, if you wanted to offset the window between a valuation date and the delivery dates. It’s all very fluid (had to).

Dawid
Userlevel 7
Badge +1

I remember how SQL Reporting Services had the cascaded filters… it was a thing of beauty 😃

Dawid
GenDemo
Userlevel 4

I have a similar question.

I have 2 filters - one is a yes/no field, the other is a string field.
 

And I would like these two to depend on one another. So that if you select Yes on the left, it automatically flicks the right hand one to ‘Pre-Legal’ and visa versa.

Spent a long time with support on this on Friday and there is no solution sadly.

pranay.goyal

Have you folks tried faceted filtering? I was able to base one filter based of another filter on a dashboard. 

https://docs.looker.com/dashboards/adding-filters-to-user-dashboards#setting_up_faceted_filters

Reply


Terms of Service