Subsets Overview

The Subset Editor can be accessed across various contexts, including within a spreadsheet, in the Modeler interface, and directly from Excel. To open the Subset Editor in Jedox Web Spreadsheet, select Query and then click on Paste Subset. To access the Subset Editor in the Modeler, double-click on any dimension, then select either a Stored Subset or create a new Subset from the drop-down menu. To open the Subset Editor in Excel, navigate to Jedox Excel Add-in and click on Paste Subset.

The Subset Editor in Jedox allows you to filter data according to specific criteria. In most sections of Subset filters, you also have the flexibility to utilize various types of inputs. These include named ranges, which offer a convenient way to reference a set of cells by name; ranges, allowing you to specify a continuous series of cells; static values, providing fixed data entries; and variables, offering dynamic options for parameterization.

When selecting a parameter for the 'Subset' option in the Subset Editor (by checking the 'variable' checkbox), you can utilize cell references, named ranges, or (in Web) @variables. However, directly entering and using a formula expression (e.g., an IF formula) for this variable in the Subset Editor is not supported.

Also, it is important to note that the "types" of variables you can select may vary depending on the context (Web Spreadsheet, Modeler, and Excel) in which the Subset Editor is opened.

How to use a Subset

When using a Subset, mark a sufficiently large area so that the display of the Subset does not get truncated. Make sure you also select at least two columns. If you paste on three columns, you can display indents.

The Preview area displays all Subset modifications simultaneously, provided that the Auto (see 1 below) option is activated. If the "Auto" option is not activated, you can update the preview by clicking the Refresh button. (2)

In the lower-left corner (3), you can see the PALO.Subset function and its sub-functions, and how they change during the creation of a Subset.

Clicking Paste inserts the Subset into the selected area of the spreadsheet. (For more information on the Paste stored button, see Stored Subsets.)

You can dynamically sort and filter your element list by using the filter tabs at the top of the Subset Editor. Click on the tabs to enter arguments or expressions for the individual filters. The General and Sort filters take effect immediately, but for Hierarchy, Text, Picklist, Attribute, and Data filters, you must set up one or multiple criteria . Checkmarks in the tabs indicate which filters are active.

Next step: Subset Editor Tabs

Check out some important articles on Subsets

Stored Subsets

Subset settings can be stored or saved for the current user or for all users. Read this article for information on the two options for storing Subsets.

Using Subsets in Jedox Views

Jedox Views offer the ability to use a stored Subset of a dimension instead of the full dimension. Read this article for more information.

Manual Editing of Subset Formulas

Besides using the Subset Editor, Subset formulas can also be edited manually, like all other spreadsheet formulas. Read this article for more information.