Manual Editing of Subset Editor Expressions


Besides using the Subset Editor, subset formulas can also be edited manually, like all other spreadsheet formulas. The example below demonstrates some of the possibilities.

Open a new spreadsheet and select cells A1:A30. Open the Subset Editor and make the the following selections:

General tab: under “Server” select Demo database and Products dimension; under “Layout options select” Hierarchy and check the box “Show parents below children”.
Sort tab: under “Sort by”, select Element name; under “Type limitation”, select All Elements.

When you click Paste, you should see the following result:

Suppose you only need lines 5 to 18.

In this case, you cannot simply delete cells, because they are part of an array. However, you can deactivate the array function using the key combination CTRL+SHIFTLOCK+ENTER. Then A1 can be edited and in the edit line you can click the PALO.SORT() part and open the function editor.

Now you can enter arguments that are not available in the subset editor:
                   Number of elem.: 14.
                   Start with: 4.

Finish the formula changes and copy the changed function in the entire range A1: A30. Then activate the array function with CTRL+SHIFT+ENTER and you will get the desired elements:

Was this post helpful?
NoYes (No Ratings Yet)