Except/ Remove
Removes members from the evaluation of the first set that also exist in the second set.
Syntax & Usage
<set definition>
A PQL expression that returns a member set. The first set definition is the set of elements that will be returned.
<set definition>
A PQL expression that returns a member set. The second set definition is the set of elements that will be excluded from the returned set.
<member>
A PQL expression that returns a single member element (can be a custom member).
A member may be given instead of a second set definition. In this case, the given member will be excluded from the returned set of elements.
Notes
- This function may be connected to an Add, Date, or Fork block.
- The second argument may be EITHER a set definition OR a member.
Example
The following expression will return a set of elements from the first day of 2010, up to and including 31 March 2010, EXCEPT the second given set definition (February 5, 6, 7, and 8):
{Except(YTD([data].[dateKey].[1269993600000],1,1),{[data].[dateKey].[1265328000000],[data].[dateKey].[1265414400000],[data].[dateKey].[1265587200000],[data].[dateKey].[1265500800000]})}
In this example, the expression will return a set of all elements in the Manufacturer hierarchy, EXCEPT the given member (Old Balance):
Except({AllMembers([manufacturers].[Manufacturer])},{[manufacturers].[Manufacturer].[Old Balance]