13 Replies Latest reply: Nov 27, 2017 3:52 AM by Simone Trabattoni

# 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.

• ###### Re: Make analysis on the excluded from a selection

Maybe have a look at the E, Excluded, Set Analysis functionality.

HIC mentions it at the bottom of this blog post Excluding values in Set Analysis

• ###### Re: Make analysis on the excluded from a selection

How can you manage the e function in a table without measure?

I think you are hinting a set analysis in the dimension: if so, how in a right manner?

The result is going to be (hopefully) a table with all the excluded places and relatives brandoftheplace.

• ###### Re: Make analysis on the excluded from a selection

Put a measure in, even if just uses the Only() aggregation function and duplicates one of your dimensions.  You can delete the dimension later, assuming it all works & tests ok.

• ###### Re: Make analysis on the excluded from a selection

Hi Bill, thanks again.

I've tried some of your ideas. I'm quite near the result, but as you can see in the app attached and refreshed, I have some problems: the un-selected item is not in the same line of the selected one, and this makes it not visualized when the product is choosen. In the second sheet of the app you can find what I mean.

How can I correct the formulae?

They are something like and both work

=aggr(only({1-\$<product=e(product)>} place),place)

and

=aggr(only({1-\$<product=(product)>} place),place)

• ###### Re: Make analysis on the excluded from a selection

take a look at 1-\$ identifier in Set Analysis

• ###### Re: Make analysis on the excluded from a selection

Almost the same answer that I gave to Bill:

how can you manage the e function in a table without measure?

I think you are hinting a set analysis in the dimension: if so, how in a right manner?

The result is going to be (hopefully) a table with all the excluded places and relatives brandoftheplace.

• ###### Re: Make analysis on the excluded from a selection

yes you should use an Aggr function in a calculated dimension.

• ###### Re: Make analysis on the excluded from a selection

Hi Andrea,

thanks again.

I've attached a refreshed app with your hints and Bill's one. There is a problem, the excluded value is not in the same line of the product, and this influences the selections. Any idea how to put them in the same line (that is not simply an above() ) function) ? Here the formulae whom work (more or less):

=aggr(only({1-\$<product=e(product)>} place),place)

and

=aggr(only({1-\$<product=(product)>} place),place)

• ###### Re: Make analysis on the excluded from a selection

I've added two product to the app (the second version had only one) and almost everything falls apart.

• ###### Re: Make analysis on the excluded from a selection

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?

• ###### Re: Make analysis on the excluded from a selection

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.

Best,

Sarah

• ###### Re: Make analysis on the excluded from a selection

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.

Sarah

• ###### Re: Make analysis on the excluded from a selection

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.