Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

Make analysis on the excluded from a selection

Hi community!

Today I'm investigating about the selections in an app.

The goal is to have a kind of analysis on excluded values.

Qlik Sense tells us everything about the selected items in a viz, but (it is my limit clearly) I cannot make it to tell me something about the excluded values.

I'm bringing an example.

I'm working on a group of products, sold (or not) in places, and each places belongs to a brand (brandoftheplace).

I can see that product 3, for example, is sold only in the 25% of the places. To see which places are not selling 3 is very simple, thanks to the power of grey, i.e. the dark grey places are not selling the product 3.

But I cannot see to which brandoftheplace the excluded 75% of the places belong, each one (clearly in this example is rather simple, but let's think in bigger contexts).

So the question is: how can I analyze (in this case find the brandoftheplace) the excluded values? Maybe a table that makes me see only the excluded? If so, how?

I do not know it's clear, but I'm attaching an app with all the data, viz and other things, hoping it helps.

Thanks in avance

EDIT: Attached app with something added after some hints.

EDIT: Attached new app modified.

13 Replies
Creator III
Creator III

I've found that a great result should be a simple filter or table that, when you select a product, shows the place where the product is not sold (i.e. the dark grey in Qlik). Exactly the opposite of the usual Qlik.

I've tried several formula and idea, and I think it should be managed with dimension, because measures say "blank" or "null" where the product is not sold, in a way that I've not found how to make it usable.

Any idea?


I have added a sheet that I think does something like you are looking for to your sample (attached).  This will show a table when you pick a particular product that contains only the places where that product is not sold.

I am using the measure below which will show 1 if the selected product is not sold in that place:

sum( {$< product =,

place = E({1<product={$(= chr(39) & if(GetSelectedCount(product), GetFieldSelections(product)) &chr(39))} >}) >} 1 )

I then further added a calculation condition so that the table won't show if you have more than 1 product picked.

You have to select the products one by one for this to work.



View solution in original post


Even better to use:

only( {$< product =,

place = E({1<product={$(= chr(39) & if(GetSelectedCount(product), GetFieldSelections(product)) &chr(39))} >}) >} place )

Which will show the name of the place instea of the number 1 if the product is not sold there.


Creator III
Creator III

Thank you,

that's what I needed. I was trying to use the GetFieldSelections() but I did not reach a feasible result, your is great.