RANGETOHIERARCHY

Converts a range to a hierarchy.

Syntax

RANGETOHIERARCHY(range; numLevels; numExpandLevels)

  1. range - the range containing the hierarchy information - Value type: range

  2. numLevels - the number of columns at the beginning of range defining the hierarchy - Value type: number

  3. numExpandedLevels - the number of levels of the hierarchy which should be expanded - Value type: number (optional, default value: numLevels)

  4. aggregationType - specifies how empty values in the additional columns of the hierarchy should be aggregated - Value type: text (optional, default value: “none”) Possible values are:

    1. "none" - no additional aggregation

    2. "sum" - using the sum of the children

    3. "min" - using the minimum value of the children

    4. "max" - using the maximum of the children

    5. "avg" - using the average value of the children

Return

The result is a range with

  • the hierarchy level in the first column

  • the display name in the next column (extracted from the first numLevels columns of range)

  • all remaining columns of range

  • all levels up to numExpandedLevels are expanded, all other levels are collapsed

Examples

In the following examples, the widget on the left is a table widget of type "Hierarchy".

Example 1 - The whole table is used as hierarchy.

Example 2 - like example 1, but with an additional column containing values for the hierarchy.

Example 3 - The hierarchy is only defined by the first 3 columns. The remaining 2 columns are added to each row of the hierarchy.

Example 4 - Parameter 3 specifies that only the first two levels of the hierarchy are expanded by default.

PreviousRANGETOCSVNextROW