Filter pane exclusive instead of inclusive?

[bashdown]

Hi all,

I would like to be able to select multiple values in a filter pane and have the rest of the data show instances where BOTH selections are true.  For example, if I had a list of projects which different employees are working on, I want to make a filter pane with employee names that I can then select and show only projects that both employees worked on.

Source:

Project	Employee
A	Bob
A	Mark
A	Steve
B	Bob
B	Mary
C	Bob
C	Mark
C	Mary

If given the above data, I want a filter pane that when I select 'Bob', the list of projects will show A, B and C.  However if I select 'Bob' and 'Mary', the list of projects will show only B and C.  If I select 'Bob', 'Mark' and 'Mary', it will only show project C. 

Right now, if I select 'Bob' and 'Mary' – the output is all three projects because either Bob OR Mary worked on one of those projects and I'm interested only in projects that Bob AND Mary worked on.

Is something like this possible?

I was hoping to use a filter pane because in reality my problem has many different charts and KPIs that I would like to link to this filter and something like a pivot table won't work.

Thanks for your help,

Brayden

[sunny_talwar]

Check here:

Generating AND, OR , NOT selection logic in Qlik Sense

[bashdown]

Thank you for the quick response.  I can't seem to get this to work for me – I'm not sure if it is because neither of my field values are numerical and both are text strings?

As another possible workaround – is it possible to have a table display more than one value for a measured field?  Using the above example, would it be possible to have:

Dimension: Project

Measure: Employee

and have the resulting table look like:

Project:                    Employee:

A                              Bob, Mark, Steve

B                              Bob, Mary

C                              Bob, Mark, Mary

Right now if I use the above settings, it only returns the first field value for each project and my result is:

Project:                    Employee:

A                              Bob

B                              Bob

C                              Bob

Thanks for your help.

[NZFei · Accepted solution]

Check attached the app please.

hope it works for you.

Fei

[sunny_talwar]

You should be able to use Concat() function here

Dimension

Project

Expression

Concat(DISTINCT Employee, ', ')

[bashdown]

This worked perfectly, thank you for the clear example (and the alternate coding in case I had mixed up my dimensions and expressions).

[bashdown]

Sorry I'm not able to mark both as correct, your solution would also have worked with the addition of the concat function.  Thanks for your help.

[bashdown]

Thanks again for your help. 

I used your solution to apply to various different KPIs and tables in the attached sheet, but I can't seem to get it to work for "Highest Redpoint Grade." 

It works great when I have one or more "Partners" selected, but before I have selected any, the KPI is blank.  I've tried many different permutations and can successfully get either the true or the false statement of the original if() to work if I use a simple string for the other, (true or false respectively).  This makes me think it is something to do with the nested if()s. 

I image my solution to getting the max grade isn't the most elegant, and if there is a better way I am always open to suggestions, but for now it is a pretty long list of nested if()s.

Thanks again in advance.

Brayden

[NZFei]

Change your vMaxGradeConversion variable to:

If you look at the key calculation for this, it always says if getselectioncount > 0, otherwise what you want to show.

Fei

[bashdown]

That worked perfectly.  I think I am finally done with this dashboard, thanks for helping over the last few days - saved me hours of trial and error.  Your responses were clear and concise with great examples.  Thank you.