Solution for filtering data by using AND + OR at the same time in Qlik Sense 3.2

bernhard_koehle — Tue, 01 Aug 2017 09:37:29 GMT

For a long time we where looking for a fast solution to filter data.

We needed something like:

Filter1 + [Option1a]+[Option1b] OR Filter2 + [Option2a]+[Option2b] OR Filter3 + [Option3a]+[Option3b] …….

Filter-n is always mandatory.

Option-na and Option-nb is optional.

So we use AND + OR at the same time.

The Qlik support told us it would not be possible without extensions.

A solution via IF is working but the integration of 3 columns is very slow.

So we are now using “set analysis”.

The solution has 4 columns with 3 filters per column. With 2.3 million dataset-rows the result appears within a second.

There is a trick to split the OR from the AND.

The OR’s are integrated in the frontend. The AND’s are realized in 8 variables.

The column is:

=Count({$(VARTAB1) + $(VARTAB2) + $(VARTAB3) + $(VARTAB4) } Customer)

One variable looks like

VARTAB1:

< Car1={'$(=getfieldselections(CarFilter1))'} , Color1={$(=if(getselectedcount(ColorFilter1)>0,getfieldselections(ColorFilter1),'*'))} , Year1={$(=if(getselectedcount(YearFilter1)>0,getfieldselections(YearFilter1),'*'))} >

For better understanding look the example app and use the bookmark or your own selections.

Have a lot of fun

PS: be careful “/” is not allowed in the colors or years

topic Solution for filtering data by using AND + OR at the same time in Qlik Sense 3.2 in App Development

Solution for filtering data by using AND + OR at the same time in Qlik Sense 3.2