Generating AND, OR , NOT selection logic in Qlik Sense

[JonnyPoole]

'AND mode' is an out of box feature in QlikView whereby a QlikView developer a list box in a QlikView application to use 'AND mode' rather than the default 'OR mode'.

In Qlik Sense, its not there out of box in the same way, but some minor SET ANALYSIS expression syntax can create the right context on a chart to show the output in an "AND" context.

Attached is a self contained application that demonstrates the technique.

Please also refer to these screenshots:

Here is the sample data... notice that Skill 'A' ,'B', and 'C' is associated only with User '1'.  But User '1', '2', and '3' are collectively associated (OR mode) with the 'A','B', and 'C' set of skills

I have created an educational UI in the app to show how the default 'OR' mode chart contrasts with an 'AND' mode technique

When i select Skills  'A', 'B' , and 'C' ... notice that the AND mode chart shows only User '1'.

But the OR mode chart shows Users '1', '2', and '3'

The OR mode expression is:   count (distinct Skills)

The measure expression on the AND mode is modified to the following.

Basically it filters the result to show only users where the unique number of skills associated to them is equivalent to the number of distinct skills selections in the UI.

hope it helps !

Version 2-3 update:  Just added a  NOT mode tab where you can combine AND with NOT and see a list of users with all the skills selected but exluding users who already have one or more skills selected from a different list.  Full description below in the comments.

[#2]

Thanks!

However, that is a pretty special case. Are there any good methods for showing (in a table) all users which have both skills A and B but not C?

[JonnyPoole]

I think that is possible.  You would introduce a 2nd user list (via data island) to capture the accounts to exclude and then update the expression accordingly.  Again i would use set analysis to do this. You can use *,-,+ operators to capture the right combination of the two sets of users.

This document is more geared at QlikView users who leveraged 'and mode'.  The direction you are referring to is getting more sophisticated .

An example can be found on the 'small business targetting' tab of the this demo. Its for QlikView but can be done in QLik Sense using similar techniques

QlikView

[#4]

Thanks! Yeah, I am trying to do this in another application, but it is hard to get the set analysis right.

I am thinking along the lines of this snippet:

count(<[Skills] = P([Data Island Skills To Have])>*<[Skills] = P([Data Island Skills Not To Have])>[User])

[JonnyPoole]

Good challenge. It wasn't the most straight forward but together with HIC's blog posts on Set Analysis I created a NEW tab in the AND mode app.

I'll post the QVF but here are some screenshots:

The new page has an 'AND' filter pane of skills as well as a new 'NOT' filter pane of skills. The 'NOT' skills are a new data island derived from the list of skills using a resident load  (Load  Skills as NotSkills resident existingdata; )

There are no users with all the skills, so the bottom left chart is empty, and since no skills have been selected in the NOT list, there are no users to exclude.

In the top right you can see that a different way: both the And List and Not List is empty.

And the bar chart just shows every user

To see how i show the logic, here i select Skills='A' and notice how Skills='A' is associated with both user 1 and user 2. 

If i step back and select Skills='C' instead, notice that Skills='C' is associated with User 1 , 3 and 5

So what happens if i start over and select  Skills='A'  and NotSkills='C' .

The AND list will result in users 1 and 2.  and the Not list will result in users 1 , 3, and 5.

So the expected result is.... user=2.

The full expression in the bar chart uses different SET Analysis depending if both filter panes are selected , one of them or neither.  If both are selected then i use this expression:

  count( distinct {<User={"=aggr(count(distinct Skills),User)=getselectedcount(Skills)"}> - <User= p( {1<Skills={$(=vNotSkills)}>} User)  >}  Skills)

  ,

The first part:    count( distinct {<User={"=aggr(count(distinct Skills),User)=getselectedcount(Skills)"}> ... ....provides the AND mode set.

And the 2nd part:   <User= p( {1<Skills={$(=vNotSkills)}>} User)  >}  Skills)

...provides the NOT set. Notice that we are removing users associated with a list of 'Not' skills. So there is an additional complexity using the p() function.

the vNotSkills is a variable with definition:

=chr(39) & concat( distinct NotSkills, chr(39) & ',' & chr(39)) & chr(39)

This will produce a comma and single quote delimitted list of the selected NOT skills with preceding and trailing single quotes added (chr(39).  This ensures that the delimitted list is compliant with Set Analysis syntax. 

Hope it helps.

[#6]

Cool! Thanks a lot. I will go through this in detail tomorrow, but it looks really helpful! Seems like a pretty common thing to do when trying to figure out what customers to target with campaigns for specific products etc.

[JonnyPoole]

Also, i uploaded the new QVF to the document which is now updated to be the 3rd version of the initial post.

[Anonymous]

Man, thank you so much! I was starting to think that there wasn't any way to achieve AND logic in Qlik Sense. This is exactly what I needed!

[shirenmathai]

Hi jpe,

I have a similar requirement of AND operation. Here i have two tables. The first table showing the Groups and count using the set expression as below. Second table showing the user details. If i select A,B,C then first table shows count as one but user details table not filtering based on selection. I need to show only that common user in table. Could you please advise how to change the set expression in my case.

if(getselectedcount(Group)>0,

count( distinct {<User={"=aggr(count(distinct Group),User)=getselectedcount(Group)"}>} User) ,

count(distinct User)

)

Users:

LOAD  * inline   [

User, Name

1, ABC

2, BCD

3, EFG

4, RTG

5, YUR

6, IOB

7, LIK

];

Matrix:

LOAD  * inline   [

Group,User

A,1

B,1

C,1

B,2

A,2

C,3

D,4

C,5

D,5

E,5

E,5

F,6

G,7

];

[ioannagr]

hi @JonnyPoole ,

this 

if(getselectedcount(Reason)>0,

count( distinct {<User={"=aggr(count(distinct Reason),User)=getselectedcount(Reason)"}>} User) ,

count(distinct User)

) 

for me counts all the distinct reasons that exist in the filter (without making any selections).

I was expecting a count of users, AFTER selecting in Reason filter. Right?

Please elaborate.

Ioanna

[BogGmit]

Hi, Thank you for sharing this post. It is very helpful. Maybe some day we see And mode in Qlik Sense. 

I'm don't know if I have latest version, but I think that there is small error on AND & NOT sheet. 

In my Opinion when I want to see  only Users who don't have skill C. To achieve this I select only one value from NotSkills 

In my option correct result should look like that: 

Users 1, 3, and 5 was excluded. 

I believe that here should be minus after User " User -= p "

We cant use e instead p  or =- because then we will don't get good results if someone select some Users.