Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bashdown
New Contributor

Filter pane exclusive instead of inclusive?

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:

ProjectEmployee
ABob
AMark
ASteve
BBob
BMary
CBob
CMark
CMary

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

1 Solution

Accepted Solutions
xufei123
Valued Contributor

Re: Filter pane exclusive instead of inclusive?

Check attached the app please.

hope it works for you.

Fei

9 Replies

Re: Filter pane exclusive instead of inclusive?

bashdown
New Contributor

Re: Filter pane exclusive instead of inclusive?

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.

xufei123
Valued Contributor

Re: Filter pane exclusive instead of inclusive?

Check attached the app please.

hope it works for you.

Fei

Re: Filter pane exclusive instead of inclusive?

You should be able to use Concat() function here

Dimension

Project

Expression

Concat(DISTINCT Employee, ', ')

bashdown
New Contributor

Re: Filter pane exclusive instead of inclusive?

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

bashdown
New Contributor

Re: Filter pane exclusive instead of inclusive?

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
New Contributor

Re: Filter pane exclusive instead of inclusive?

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

Highlighted
xufei123
Valued Contributor

Re: Filter pane exclusive instead of inclusive?

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
New Contributor

Re: Filter pane exclusive instead of inclusive?

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.

Community Browser