Except/ Remove

Removes members from the evaluation of the first set that also exist in the second set.

Syntax & Usage

Except(<set definition>, <set definition> OR <member>)

<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]