topic Changing filters on multiple variables simultaneously in QlikView

Changing filters on multiple variables simultaneously

Wed, 28 Dec 2011 10:57:23 GMT

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

Changing filters on multiple variables simultaneously

Wed, 28 Dec 2011 12:19:55 GMT

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.

Changing filters on multiple variables simultaneously

Wed, 28 Dec 2011 13:04:28 GMT

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

Re: Changing filters on multiple variables simultaneously

johnw — Wed, 28 Dec 2011 17:29:23 GMT

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.

Re: Changing filters on multiple variables simultaneously

Thu, 29 Dec 2011 08:18:59 GMT

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