Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

simotrab
Contributor II

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.

1 Solution

Accepted Solutions
sarahplymale
Contributor

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

13 Replies

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

agigliotti
Honored Contributor II

Re: Make analysis on the excluded from a selection

take a look at 1-$ identifier in Set Analysis

simotrab
Contributor II

Re: Make analysis on the excluded from a selection

Thanks for your answer!

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.

simotrab
Contributor II

Re: Make analysis on the excluded from a selection

Thanks for your answer!

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

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.

agigliotti
Honored Contributor II

Re: Make analysis on the excluded from a selection

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

simotrab
Contributor II

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)

simotrab
Contributor II

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)

simotrab
Contributor II

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.

Community Browser