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

[nayanmistry]

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!!!

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

[felipedl]

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:

Exclude archived:

Exclude both:

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

[nayanmistry]

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!