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

[bernhard_koehle]

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