0 Replies Latest reply: Aug 1, 2017 5:37 AM by Bernhard Köhler RSS

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

    Bernhard Köhler

      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

       

       

      Cars.JPG