Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
nayanmistry
Partner - Contributor II
Partner - Contributor II

How to use to 1 filter to exclude values or multiple values

Hello!

Say I have a list of 10 projects each with its own project status. 

I want to be able to create one filter that will list 3 options:
- Filter all the complete projects.
- Filter all the archived projects.
- Filter both all the complete and archived projects.

Filter:
- Exclude 'Complete' (will result in my table showing (10-3) 7 projects.)
- Exclude 'Archived' (will result in my table showing (10-2) 8 projects.)
- Exclude both 'Complete and Archived' (will result in my table showing (10-3-2) 5 projects.)

I can do this by creating each filter expression in its own filter but my client would prefer to have all 3 squished into 1.
I'm running into some difficulty combining all 3 expressions into 1.

Please help! (Data table below.)

Thank you in advance!!!

IDProjectProject_Status
1PRO1Complete
2PRO2Complete
3PRO3Complete
4PRO4Live
5PRO5Live
6PRO6Archived
7PRO7Archived
8PRO8Pending
9PRO9Paused
10PRO10Paused
Labels (2)
2 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi, its a lenghty explanation, but you can achieve it with a dummy dimension and a dynamic set analysis for exclusion (subtraction) of sets.

 

First step, load the data:

// dummy dimension for you to select data on, the filters you mentioned

dummy_dim:

load

// gets a numeric representation of the text, based on its row position

dual(Status,RowNo()) as Status;
Load * Inline
[
Status
Exclude Complete
Exclude Archived
Exclude Complete and Archived
];

// creates a mapping for the status you want to exclude

Mapping
Status_dim:
Load * Inline
[
Status,Value
Complete,1
Archived,2
];

// load your data

Data:
Load
*,
ApplyMap('Status_dim',Project_Status,0) as Value;
load * Inline
[
ID,Project,Project_Status
1,PRO1,Complete
2,PRO2,Complete
3,PRO3,Complete
4,PRO4,Live
5,PRO5,Live
6,PRO6,Archived
7,PRO7,Archived
8,PRO8,Pending
9,PRO9,Paused
10,PRO10,Paused
];

 

Next, create a variable (ive named it dynamic_set) and put the value as the following:

=pick
(
Match
(
Num(Status),
1,
2,
3
),
'1-<Value={1}>',
'1-<Value={2}>',
'1-<Value={2}>-<Value={1}>'
)

 

This will select the according set analyis to be put in the expression to show only the current fields you need

1: excludes complete,

2: excludes archived

3: excludes both of the above

 

On a chart, paste the expression:

only({$(dynamic_set)}Project_Status)

 

And on the dashboard, use the Status field as a filter to get the selections on the exclusions as shown below (see the table on the upper right corner):

Exclude complete:

sample1.png

Exclude archived:

sample2.png

Exclude both:

sample3.png

Attached also the QVW i used (same logic works on Sense, View is quicker to do).

nayanmistry
Partner - Contributor II
Partner - Contributor II
Author

Hello!

Thank you for your assistance with this!

Sorry to be difficult but this piece of work is bespoke to one of our clients and I can't interfere with the master data model and data load script hence I'm limited to trying to solve this problem by expression writing only within the dashboard itself.

Any thoughts? I appreciate your help.

Thank you!