Example: Combining the Intersect and Except Functions

The Intersect function combines selections between members. The Exclude function excludes members from a selection.

You can combine these functions to narrow your results. In this example, we will create the following function: Except the Descendants of ‘Total Equity’ (250000) from the Intersection of Descendants of ‘Balance Sheet’ AND Level 1.

Figure 10-1 Except Function


screenshot showing except function parameters, described as follows

  1. Begin by selecting a member, click the function button, and then select View All Functions.

  2. Select the Except function.

  3. In the Parameters panel, click member selector next to Member Set 1. Figure 10-1 shows the Except function with the member select button highlighted.

    This opens a second iteration of function builder that enables you to nest an Intersect function inside the Except function.

  4. Select Intersect, and then enter Descendants of BalanceSheet in Member Set 1, and Level 1 in Member Set 2.

    Figure 10-2 Intersect Function


    screenshot showing intersect function as described in current step

  5. Click Select, and then click OK.

  6. In Member Set 2, enter Descendants of 25000.

Figure 10-3 Result of Except and Intersect Functions


screenshot showing the result of the function as described below

Figure 10-3 shows the results. The members returned include all of the level 1 descendants of Balance Sheet, except for those that are also descendants of Total Equity.