article Generating AND, OR , NOT selection logic in Qlik Sense in Qlik Sense Documents

Generating AND, OR , NOT selection logic in Qlik Sense

JonnyPoole — Thu, 13 Aug 2015 16:17:39 GMT

'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.

Re: Generating AND, OR , NOT selection logic in Qlik Sense

Tue, 20 Oct 2015 09:20:44 GMT

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?

Re: Generating AND, OR , NOT selection logic in Qlik Sense

JonnyPoole — Tue, 20 Oct 2015 20:39:45 GMT

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

Re: Generating AND, OR , NOT selection logic in Qlik Sense

Tue, 20 Oct 2015 21:13:09 GMT

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])

Re: Generating AND, OR , NOT selection logic in Qlik Sense

JonnyPoole — Thu, 22 Oct 2015 13:03:07 GMT

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.

Re: Generating AND, OR , NOT selection logic in Qlik Sense

Thu, 22 Oct 2015 13:38:33 GMT

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.

Re: Generating AND, OR , NOT selection logic in Qlik Sense

JonnyPoole — Thu, 22 Oct 2015 13:49:34 GMT

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

Re: Generating AND, OR , NOT selection logic in Qlik Sense

Anonymous — Thu, 09 Feb 2017 13:50:57 GMT

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!

Re: Generating AND, OR , NOT selection logic in Qlik Sense

shirenmathai — Mon, 26 Mar 2018 09:22:42 GMT

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

F,6

G,7

];

Re: Generating AND, OR , NOT selection logic in Qlik Sense

ioannagr — Thu, 18 Feb 2021 14:07:29 GMT

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

Re: Generating AND, OR , NOT selection logic in Qlik Sense

BogGmit — Fri, 13 Oct 2023 09:13:36 GMT

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.

Re: Generating AND, OR , NOT selection logic in Qlik Sense

ogatchell — Wed, 22 Nov 2023 00:28:18 GMT

Hi BogMgit,

Thanks for correcting Jonny's script. It works great! His script appears to be designed for use with one (target) field for one chart. I have 50 (target) product attribute fields! I can apply Jonny's script with your correction to each field, they work fine, but, as you might expect, each chart in which the target field appears is silo'd.

Are you working with more than one field and by chance merging the fields? if yes, can you share how you do this?

Oliver

Re: Generating AND, OR , NOT selection logic in Qlik Sense

ogatchell — Sat, 02 Dec 2023 23:43:14 GMT

The script appears to work with multiple fields simply by intersecting "*" the scripts of each respective field. For example, here's how Jonny's script with the "Skills" field looks with a 2nd field, "State"; if anyone has a better approach, please weigh in:

if(getselectedcount(Skills)>0 and getselectedcount(NotSkills) > 0,

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

if(getselectedcount(Skills) > 0 ,

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

if(getselectedcount(NotSkills) > 0 ,

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

count(distinct Skills)

)

if(getselectedcount(State)>0 and getselectedcount(NotState) > 0,

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

if(getselectedcount(State) > 0 ,

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

if(getselectedcount(NotState) > 0 ,

count( distinct {<User -= p( {1<State={$(=vNotState)}>} User) >} State) ,

count(distinct State)

)

Re: Generating AND, OR , NOT selection logic in Qlik Sense

ogatchell — Sun, 03 Dec 2023 00:26:26 GMT

Does Jonny's solution preclude the use of OR logic in the chart, which displays output of AND and NOT selections?

In Qlikview, Qlik executes AND and NOT logic operation in a single list box. A user can apply AND logic, NOT logic or both in this box. In addition, this box and a standard OR logic list box can be placed side-by-side, and selections made in either box. In other words, OR, AND and NOT logic can be applied to the same field. While AND and NOT logic selections may remove selection previously made or preclude OR selections subsequently made, OR selections are still be possible. In other words, AND and NOT selections do not necessarily preclude OR selections.

Whereas, Jonny's script operates with separate AND and NOT logic filter panes (list boxes); and, the "AND" list box is actually a standard OR logic list box; but, it operates as an AND list box within the chart that displays the output of the AND and NOT selections.

To answer my question, I think it does preclude the use of OR logic in the chart. But please correct me if this is wrong.

Btw, how can Qlik Sense reasonably support human thought in Natural Language Processing if a user can't exercise OR, AND and NOT logic at will in the selection of field values?