Excluding Options in Filter Pane while Allowing Multiple Selections

[mattb79]

Relatively new developer here...

I am familiar with using the Aggr() function to hide excluded values in a filter pane...but in my current application, it also limits the user to a single selection.

Consider a case where I have two filter panes...the first for State, the second for City. When the user selects a State, I'd like for the City filter to only display cities that are in the selected State. So I am using the expression '=aggr(city,city)'. This works just fine. However, as soon as the user selects a City, all other cities from the selected state disappear making it impossible for the user to select multiple cities within the state. Is there anyway to modify my expression to allow the user multiple City selections while still limiting the list to cities within the selected State?

[petter]

It would be normal to have State and City associated with each other in the data model. Most often in the same table.

If you do then selecting a state will filter the cities to only cities belonging to the state without any extra effort. You will also be able to multi-select cities if you want to.

[mattb79]

It's not that the fields aren't associated...they are and are being loaded from the same table...and you are correct that Qlik automatically filters down the cities based on state selection.

But the issue is that the default behavior is to simply grey out the cities that are not within the selected state...I want to hide those cities completely. From what I've read, this can be achieved with the expression =aggr(city,city) as I mentioned...but this then limits the user to a single selection for city. 

Hopefully the attached image will illustrate the point better...top is Qlik default. The bottom is using the aggr function. The bottom is almost the behavior I need...except that when a city is selected the remaining cities disappear limiting the user to a single selection of city for the selected state.

[petter]

Actually you might be able to achieve this by using "data brushing". A technique explained in detail in this blog post:

https://www.ometis.co.uk/data-brushing-qlik-sense/

I have an almost finished proof-of-concept in Qlik Sense which you can have a look at.

Using two tables instead of two filter panes - but it looks kind of ugly but behaves almost like you would like.

I am running out of time just now so I attached the proof-of-concept even though it is just 80% or so done... see what you can make out of it.

[petter]

Please click the like button or heck if the response was correct you could even mark it as a correct solution.
Believe me - it will be greatly appriciated by the contributors ...
We love likes as much as anyone posting anything on social media

[mattb79]

Thanks again for the response and idea...but unfortunately, even if I can get the functionality to work, aesthetically it won't work for me. 

My other thought was to create a variable to store the state selection, then use the expression...

=if(state=var_state,city,null())

where var_state is defined as =only(state). Logically I would think this would work...as if I replace the variable above with a hardcoded state (i.e. =if(state='CT',city,null())), then I get the behavior I'm looking for, assuming the user is only interested in 'CT'. But when I sub in the variable var_state, my city filter list is blank even when a single state is selected. Is this because the variable is not being updated dynamically within the expression as the user interacts with the filters?

[nalla777]

Thankyou it works well.. understood with the help of image that you shared..