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: 
sjenkins
Contributor
Contributor

Set Analysis to Exclude Values

I have a set of 100 buildings with unique ids. They are connected by building ID to a table of Groups, A, B,C, D....

Not all of the buildings are in a group and a building could be in multiple groups.

I have a list box with the Groups that allows me to pull the relevant buildings in a group, but I would like to have another Straight Table that show Buildings that are NOT in the selected Group. If I choose Select Excluded on the Group List Box that won't work because it won't show Buildings that have no Group. Can this be done with Set Analysis?

Any help would be appreciated,

Steve Jenkins

9 Replies
Not applicable

Try adding {1-$} as Set Analysis to your expression. i.e. Sum({1-$} Sales)

That should give you everything except items in the selection. I believe that should get you anything that is not in the selected group including those without a group.

sjenkins
Contributor
Contributor
Author

One thing I forgot to mention. I have 30 dimensions on the page and this is the only one that would be the basis of an exclusion. Is there a way to have the 1-$ only apply to this one particular dimension.

Thanks for your help.

Steve

Miguel_Angel_Baeyens

Not knowing your data cloud, it culd be interesting to do something like

=Sum({< Building -= 'A'>} Sales)
will sum all Sales except from building 'A'.

sjenkins
Contributor
Contributor
Author

Is there a way to make it for whatever is selected rather than 'A'

sum({<Building-$>}) - this doesn't work but it could just be the format.

Thanks

Miguel_Angel_Baeyens

When you do

Sum({1-$} Sales)
means "any Sales but current selection".

Not applicable

I had the same answer in mind as Miguel. Smile

sjenkins
Contributor
Contributor
Author

But that excludes the other dimensions as well. What I want is for it to use the other dimension selections normally (there's about 30 filters on the page), but only excludes the selected item in building. So the 1-$ only pertains to one selected field.

Not applicable

In the help file i found this example:

sum( {$<~Ingredient = {“*garlic*”}>} Sales )
returns the sales for current selection, but with a forced exclusion of all Ingredients containing the string ‘garlic’.

However the help also states this is only possible if the field is in and-mode

Not applicable

We've had success exluding a single field's selections while using all other selections with the following code:

sum( {$<Building=>} Sales )

It essentially is the set modifier that includes all building selections all the time, which is the same as ignoring what is selected in the building field