Is it possible to filter on multiple selections in the same filter pane using AND logic?

[allotpayg]

I have a table of doctors and their specialities. Each doctor can have upto 4 specialties.

The same specialty can appear under any one of those 4 specialties i.e. OBG can appear under Specialty 1, 2,3 or 4.

The filter pane has the list of all specialties. Is it possible to select more than one speciality in the filter pane and details of doctors who have all the filtered specialties should appear. So, when the user filters on GP, HO and IG, details of Doctor Christina Mitchell (and other doctors who have all the three specialties) should appear.

I am attaching the data file and QVF. I am developing the app in Qlik Sense Cloud and cannot use extensions.

PS: I understand QlikView has a feature like this as explained in And-Mode: How to enable AND and NOT selections with Green, White, Grey...and Red!

[shubham_singh · Accepted solution]

Yes it is possible.

I bought the Specialty column to the Doctor details table instead of another non-linked table.

Then I created a variable that given me number of selections in Specialty(gives 4 if greater than 4).

Then I used following expression in dimension of Doctor name.

pick(1+$(vSelectedSpecialty),

aggr(only(Doctor_Name),Doctor_Name),//1

aggr(only({

<Specialty_1=P(Specialty)>+

<Specialty_2=P(Specialty)>+

<Specialty_3=P(Specialty)>+

<Specialty_4=P(Specialty)>

}Doctor_Name),Doctor_Name),//2

aggr(only({

<Specialty_1=P(Specialty),Specialty_2=P(Specialty)>+

<Specialty_2=P(Specialty),Specialty_3=P(Specialty)>+

<Specialty_3=P(Specialty),Specialty_4=P(Specialty)>+

<Specialty_1=P(Specialty),Specialty_4=P(Specialty)>+

<Specialty_1=P(Specialty),Specialty_3=P(Specialty)>+

<Specialty_2=P(Specialty),Specialty_4=P(Specialty)>

}Doctor_Name),Doctor_Name),//3

aggr(only({

<Specialty_1=P(Specialty),Specialty_2=P(Specialty),Specialty_3=P(Specialty)>+

<Specialty_1=P(Specialty),Specialty_2=P(Specialty),Specialty_4=P(Specialty)>+

<Specialty_1=P(Specialty),Specialty_4=P(Specialty),Specialty_3=P(Specialty)>+

<Specialty_4=P(Specialty),Specialty_2=P(Specialty),Specialty_3=P(Specialty)>

}Doctor_Name),Doctor_Name),//4

aggr(only({

<Specialty_1=P(Specialty),Specialty_2=P(Specialty),Specialty_3=P(Specialty),Specialty_4=P(Specialty)>

}Doctor_Name),Doctor_Name)//5

)

[allotpayg]

Many thanks shubham.singh for your help.

I am a new user of Qlik and your use of Set Analysis is very instructive to me

[abhishek_singla]

Hi Allot,

Following code is sturdy. This is possible at script level. You can even add more specialty columns and all you need to just load the ETL.

You can do that, this way:

    1. Create new table of distinct Doctor_Id and their corresponding specialties.

    2. Now two table Doctor_Detailed table and New table associated on Doctor_ID column.

Now on front end, all you need to write this code in Doctor_Id(or replace with Doctor_Name) column:

IF(

  $(=GetSelectedCount(Specialty))<=1,

  Aggr(Only(Doctor_ID),Doctor_ID)

    ,

  If(

  Aggr(Count(Specialty),Doctor_ID)=$(=GetSelectedCount(Specialty))

        ,

        Aggr(Only(Doctor_ID),Doctor_ID)

      )

  )

I've attached .QVF file below.