Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
simotrab
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.

1 Solution

Accepted Solutions
sarahplymale
Creator
Creator

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

View solution in original post

13 Replies
Anonymous
Not applicable

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
Partner - Champion
Partner - Champion

take a look at 1-$ identifier in Set Analysis

simotrab
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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.

Anonymous
Not applicable

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
Partner - Champion
Partner - Champion

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

simotrab
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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