Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
take a look at 1-$ identifier in Set Analysis
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.
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.
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.
yes you should use an Aggr function in a calculated dimension.
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)
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)
I've added two product to the app (the second version had only one) and almost everything falls apart.