Data Tab in the Subset Editor

Related main article: Subsets Overview

The PALO.DFILTER() is a compute-intensive function, so the auto-refresh option in the preview is deactivated by default when this data filter is activated.The performance of DFilter subsets largely depends on the size of the cube slice being calculated. When the slice settings contain many 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):

Palo Subset formula screenshot

For this filter, the criterion, operator, and value for comparison can be defined:

As an example, a simple slice over the months "Jan, Feb, Mar" and the other elements shown above are selected here.

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.

Variables as slice operators

There are two options for using variables as slice operators.

  1. Set up the variable to point 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.

  2. If you uncheck the first variable of slice operators shown above, then you can select an operator and you can select a variable for the operand (variable must be defined as a value or as a cell reference). The procedure for the third shown variable possibility is the same.

After entering the value in the slice operator, you should press the ENTER key to propagate the formula.

The following is a screenshot showing the output of the Subset Editor with several settings for criteria and different compare operators and values:

Subset in spreadsheet

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

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.

Updated June 5, 2023