Related main article: Subsets Overview
In General tab you select the dimension for the displayed dimension elements. In the screenshot below this is the dimension Products of the database Demo.
To select options in the Data tab, you must first add a Data filter.
Note: The DFilter formula generated by the Subset Editor pastes only element names and as such does not contain any information about the order in which the dimensions should appear. This behavior is similar to that of the PALO.DATA*() functions. When changing the layout of the cube, especially the order of the dimensions, you need to manually revise the elements selected within the Subset Editor.
As an example, we have added a first data filter with following options:
Pulldown menus (1)
Most options have a pulldown menu to change the selection. The default selection is static value (1).
Criteria (2)
As criteria you can select one of the following options:
Slice operators (3)
Most of the slice operators are self-explanatory, others are described now:
Setting of slice operators as named range
For this setting first create a named range which points to a matrix of either two or four cells, where the odd cells contain the operator, and the even ones contain the value. Valid examples for a two-cell matrix are A1:B1 and A1:A2. Valid examples for a four-cell matrix are A1:D1, A1:A4, and A1:B2.
Then you can select a named range in the first pulldown menu.
For the values in the named range you can also use variables.
Handling of string or numeric search criteria
When searching for a string value, the Subset Editor automatically generates a formula with the search criteria in quotes (e.g. {"="."textstring"}), as long as the search criteria consists of letters.
When searching for a numeric value, the number must be entered with quotes in the search field. For example, you have to enter "1" (with quotes) in the search field if you want to look for the string value 1. In that case, the Subset Editor will generate the correct search term to find string values: {"="."1"}. If you enter a numeric value without quotation marks (e.g. 1) in the search field, the Subset Editor will generate the following search string: {"=".1}, which is treated as an empty string.
The following is a screenshot showing the output of the Subset Editor with several settings for criteria and different compare operators and values:
In this example, the criterion "All elements > 200" returns elements for which the values of "Jan" AND "Feb" AND "Mar" are all greater than 200.
Optional settings (4)
In the Optional settings, you may choose the following:
-
Take topmost elements: by checking this box, the filter brings the topmost elements in the amount defined in the spin box on the right-hand side.
-
Take all upper elements up to %: the filter brings all upper elements up to a percentage defined in the spin box on the right-hand side. The calculation when determining the scope of the respective range is not exact but an approximation, which can lead to unexpected behavior in some cases - for example, a filtering defined at 95% can go up to 97%.
-
Take all lower elements up to %: this checkbox takes all lower elements up to a percentage defined in the spin box. The calculation here is also an approximation, which can lead to the same unexpected behavior explained above, that is, a filtering defined at 95% can go up to 97%, for example.
Multiple filters in Data tab
If you add a second filter in Data tab, the filtered elements of the second filter will be added to the filtered elements of the first filter. Note that without checking „Intersection“, the second filter uses the same resulting elements of the previous tabs like the first filter (filters are connected with logical OR). But if you check the option „Intersection“ on the second filter, this filter will only use the resulting elements of the first filter on this tab (filters are connected with logical AND).
When using multiple DFilters, the "Value" option from the Sort tab will use the values of the last DFilter for sorting. If an element in the Subset result is not part of the last DFilter, the value from the previous DFilter will be used for this element.
Final remarks
The performance of DFilter Subsets largely depends on the size of the cube slice being calculated. When the slice settings contain many big or unspecified dimensions, the cube slice gets larger and the complexity of the calculation increases.
If possible, at least one element should be specified for all dimensions, and we advise against using the asterisk (*) wildcard character in the PALO.SUBCUBE part of the PALO.Subset formula (indicated with arrows below):
Updated November 4, 2024