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.