Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Changing filters on multiple variables simultaneously

Hi,

I have two tables like attached below (just an example) with two similar variables Continent for Client and Region for Person. What I would like to have is one list box that would allow me to change the filters on both variables in a way that if Continent=Europe then Region=Europe. Is there a way to set this up somehow? (and I don't want to create a new table with both Client and Person in it and one "location" variable). I know that it would be easy to have two list boxes and just change the filters on both of them but I would really appreciat if someone knew a more elegant solution (and I actually need it).

Client     Continent

ABC       Europe           

XYZ        Asia

DEF       North America

Person   Region           

John       Asia

Mike       Europe

Pete       North America

Thanks in advance for your help,

Marcin

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

I did this once with macros before, back before there were actions.  Here's an example:

sub setMonth()
ActiveDocument.getField("Display Month").selectValues ActiveDocument.getField("Month").getSelectedValues
end sub

It's triggered on select or on change of the field "Month", not on activate or deactivate of the list box.

I'm sure there's some correct expression to do it with actions, which would be preferable.

View solution in original post

4 Replies
Not applicable
Author

You could probably do this with actions.

Right-click the sheet and go to properties, then the triggers tab. Find your list box for Continent, and add an action. Make the action a 'Selection in field' action. make the field 'Region', and in the search string, create an expression to make it equal to the Continent selection. Something like

=GetFieldSelections(Continent)

Should work. If you have to deal with multiple selections, this may not work, but give it a try.

Not applicable
Author

Hi,

This does work to some extent because the trigger works when the list box is either activated or deactivated (depending which you choose) when the filter is on Contintent. So for example (lets say the triggers is set off when I deactivate the list box) if I set the Continent to Pan Europe I have to click somewhere on the report, outside the Continent list box, for it to be applied on the Region variable. If I use the trigger that's set off on activating the list box then I have to first deactivate and then activate the list box.

Additionally it doesn't seem to work with multiple selections like you've said.

Thanks,

Marcin

johnw
Champion III
Champion III

I did this once with macros before, back before there were actions.  Here's an example:

sub setMonth()
ActiveDocument.getField("Display Month").selectValues ActiveDocument.getField("Month").getSelectedValues
end sub

It's triggered on select or on change of the field "Month", not on activate or deactivate of the list box.

I'm sure there's some correct expression to do it with actions, which would be preferable.

Not applicable
Author

Hi John,

Thank you, this was very helpful. The macro works for multiple selections as well. I've used Triggers under Document Properties and then in Field Event Triggers I've found my Continent variable and put the macro as an action for both "On Select" and "On Change" to ensure that the filters are execty the same on Region when I select or deselect values from Continent the list box (used your macro just changed the names of the variables):

sub setMonth()
ActiveDocument.getField("Region").selectValues ActiveDocument.getField("Continent").getSelectedValues
end sub

One important thing was to add the macro to Field Event Triggers and not Sheet Object Event Triggers (which only allow "OnActivate" and "OnDeactivate") - adding some explanation in case someone else has the same problem.

Thanks and take care,

Marcin