Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Tags (1)
9 Replies
Highlighted
Not applicable

Set Analysis to Exclude Values

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.

Highlighted
Contributor
Contributor

Set Analysis to Exclude Values

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

Highlighted

Set Analysis to Exclude Values

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

Highlighted
Contributor
Contributor

Set Analysis to Exclude Values

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

Highlighted

Set Analysis to Exclude Values

When you do

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

Highlighted
Not applicable

Set Analysis to Exclude Values

I had the same answer in mind as Miguel. Smile

Highlighted
Contributor
Contributor

Set Analysis to Exclude Values

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.

Highlighted
Not applicable

Set Analysis to Exclude Values

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

Highlighted
Not applicable

Set Analysis to Exclude Values

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