topic Set Analysis to Exclude Values in QlikView

Set Analysis to Exclude Values

sjenkins — Fri, 20 Nov 2009 01:55:45 GMT

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

Set Analysis to Exclude Values

Fri, 20 Nov 2009 04:46:51 GMT

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.

Set Analysis to Exclude Values

sjenkins — Fri, 20 Nov 2009 09:33:36 GMT

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

Set Analysis to Exclude Values

Miguel_Angel_Baeyens — Fri, 20 Nov 2009 14:24:21 GMT

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

Set Analysis to Exclude Values

sjenkins — Fri, 20 Nov 2009 23:24:37 GMT

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

Set Analysis to Exclude Values

Miguel_Angel_Baeyens — Sat, 21 Nov 2009 00:10:04 GMT

When you do

Sum({1-$} Sales)

means "any Sales but current selection".

Set Analysis to Exclude Values

Sat, 21 Nov 2009 00:19:43 GMT

I had the same answer in mind as Miguel.

Set Analysis to Exclude Values

sjenkins — Sat, 21 Nov 2009 02:38:01 GMT

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.

Set Analysis to Exclude Values

Tue, 07 Feb 2012 11:20:21 GMT

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

Set Analysis to Exclude Values

Thu, 03 May 2012 14:34:02 GMT

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