Dynamic Filters (Range)

dingo · 02-11-2021 05:47 AM

Hello,

I m workin on a task where I need to allow the program numbers to be entered by users dynamically. i.e, Users should enter BETWEEN 19 AND 21 OR BETWEEN 27 AND 30 OR BETWEEN 32 and 35 and I should take those numbers and display results.

For Ex, In the derived table SQL,
SELECT * FROM TABLE WHERE

age BETWEEN 19 AND 21

OR BETWEEN 27 AND 30

OR BETWEEN 32 and 35 ;

How do I achieve this in Looker? I cant use Templated filters since they are only for logical results (i.e, age =’1’ or age IN (1,2)). Can I use parameters in SQL derived table?

Can someone please help? Thanks in Advance.

shaandalwadi

Hi @dingo ,

Are users going to be doing this from an Explore? If so, it should be possible to achieve this through the Explore filters. Clicking on the + button on the right-hand-side of a filter adds an additional line on that filter with OR logic, e.g..

Does this achieve what you need?

Alternatively, if you want your users to be entering the free text in SQL syntax “BETWEEN 19 AND 21 OR BETWEEN 27 AND 30 OR BETWEEN 32 and 35” then you may be able to use Liquid.

Best wishes,

Shaan

dingo

@shaandalwadi It’s not using explores. I need it to be done dynamically. users should be able to enter any age ranges, not just 3. Basically It changes every week.

So, After having the dashboard ready, I want to give users a filter to enter these ranges (which can be changed anytime). Based on the filter value, whole metrics in the report should be changed. If I use liquid, I wont be able to do that. please correct me if I am wrong here.

IanT

You can add the filter to the dashboard and then the user has to select matches advanced and know the syntax to enter...not ideal but you could add an html tile at the top of the db with instructions and examples.

You could also use cross filtering to drive your dashboard. You can then ctrl click all the possible combinations or values from a Table which contains just a list of Ages. You will have a little challenge making the table look nice and it be intuitive though.

I use this visualisation at the top of the dashboard, all charts below will switch to the dimension selected.

You wont be able to save the state of cross filtering and there are some other bits which might not make this ideal such as the filters not being applied to schedules, downloads or alerts.

shaandalwadi

Hi @dingo, thanks - that makes sense. @IanT makes a good suggestion for this in that case!