Subset Example: Base Subset


Below you can see a base subset. If no sorting filter is specified, elements will be displayed in the order of definition (creation). Take a look at the structure of the function in the formula bar. There you can see the base filter that is the pure subset part:  =PALO.SUBSET(Server/Database,Dimension,Indent,Alias). In this example, the “Alias” argument is left empty.


If you want to display the indent information, then you must extend the array function over three columns:

With indent 1 (or empty) and without other functions, the indent information of the elements is 0 for all.

Depending on the argument, 1, 2, 3, or 4,  different indents will be displayed. For the filter displays themselves, the indent argument only takes effect if the hierarchy level has been specified in a subset filter.

Indent 1:

The indent information of the elements is 0 for all with Indent 1 alone. Indent 1 needs PALO.SORT() or PALO.HFILTER() to display the hierarchical numbers. The hierarchical numbers 1, 2, or 3 will be displayed; for example,  in combination with PALO.SORT(), the first argument is 1 or 2.

In general, in the written case, the elements of the highest hierarchy level get the number 1, the second highest level gets number 2, and so on.

Indent 2:

With indent 2, the hierarchy numbering of the elements is 0,1, 2.

In general, elements in the lowest hierarchy level (base elements) get the number 0. The number is incremented by 1 for every step up in the hierarchy.

Indent 3:

With indent 3, the hierarchy numbering of the elements is 2, 1, 0.

In general: the elements of the highest hierarchy level get the number 0, the second highest level gets the number 1, and so on.

Indent 4:

This value will dynamically adjust the indent of elements in the subset based on the overall subset results. All elements for which no parent element is found will get indent number 1, regardless of their position level in the dimension. Elements for which at least one parent element is found in the subset will get an indent number calculated from their parent’s indent in the result (incremented by 1).

For Dimension “Months” of database “Demo”, indent 4 has the same result as indent 1 in the screenshot below, because there are no dynamic changes in this dimension.

  • The subset for indent 1 is shortened. Due to the sorting of PALO.SORT(1), all hierarchical numbers are displayed in the first three lines .
  • Indent 2 and indent 3 don’t require a combination with other functions to display the hierarchical numbers.