Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Not knowing your data cloud, it culd be interesting to do something like
will sum all Sales except from building 'A'.=Sum({< Building -= 'A'>} Sales)
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
When you do
means "any Sales but current selection".Sum({1-$} Sales)
I had the same answer in mind as Miguel.
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.
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
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