Skip to content

Hierarchy nodes and hierarchy node properties

Hierarchy node properties are available as scalar variables in any projection node that uses the hierarchy node.

How to configure Hierarchy Nodes and their properties

Using an Excel model

On the Hierarchy sheet, you will find adjacent named ranges called HierarchyTreeInclHeaders and HierarchyPropertiesInclHeaders. These define the hierarchy tree and the hierarchy node properties.

An optional HierarchyPropertiesSettings table (usually on the Globals sheet) may provide settings for each property:

  • ResolveMulti: Determines how multiple properties are resolved into a single property for the current hierarchy node:
    • Error (default): If the values are not all the same, the property resolves to NA() (#N/A).
    • Mean: Use the arithmetic mean of the values.
    • Max: Use the maximum value.
    • Min: Use the minimum value.
    • FirstOccurrence: Use the first value.
    • LastOccurrence: Use the last value.

The easiest way to understand how this works is through the examples below.

Using a JSON or YAML model

Using a JSON model or YAML model is more flexible than using the Excel model, but more verbose, and therefore a bit harder to write manually.

There are three keys:

  • hierarchy_nodes: A list of objects representing the hierarchy nodes. Each object contains:
    • name: The name of the node, which must be unique among the node's siblings.
    • level: The level of the node.
    • uuid: A unique identifier for the node. This must be unique within the entire hierarchy.
    • properties: Key-value pairs representing the hierarchy properties. The values are stored as formula strings. E.g., dates are represented as "DATE(2020,12,31)".
  • hierarchy_inheritance: A list of objects representing the parent-child relationships between the nodes. Each object contains:
    • parent: The unique identifier of the parent node.
    • child: The unique identifier of the child node.
  • hierarchy_levels: A list of objects representing the levels of the hierarchy. Each object contains:
    • level: The level number.
    • name: The level name.

The easiest way to understand how this works, is through the examples below.

Examples

A simple hierarchy without properties

Say the HierarchyTreeInclHeaders range looks like this:

Level3 Level2 Level1
TopNode NodeA NodeA1
TopNode NodeA NodeA2
TopNode NodeB NodeB1
TopNode NodeB NodeB2

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 3
    properties: { }
  - name: NodeA
    uuid: NodeA
    level: 2
    properties: { }
  - name: NodeB
    uuid: NodeB
    level: 2
    properties: { }
  - name: NodeA1
    uuid: NodeA1
    level: 1
    properties: { }
  - name: NodeA2
    uuid: NodeA2
    level: 1
    properties: { }
  - name: NodeB1
    uuid: NodeB1
    level: 1
    properties: { }
  - name: NodeB2
    uuid: NodeB2
    level: 1
    properties: { }

hierarchy_levels:
  - level: 3
    name: Level3
  - level: 2
    name: Level2
  - level: 1
    name: Level1

hierarchy_inheritance:
  - parent: TopNode
    child: NodeA
  - parent: TopNode
    child: NodeB
  - parent: NodeA
    child: NodeA1
  - parent: NodeA
    child: NodeA2
  - parent: NodeB
    child: NodeB1
  - parent: NodeB
    child: NodeB2

The following hierarchy will be available in the model:

TopNodehLevel = 3NodeAhLevel = 2NodeBhLevel = 2NodeA1hLevel = 1NodeA2hLevel = 1NodeB1hLevel = 1NodeB2hLevel = 1
TopNodehLevel = 3NodeAhLevel = 2NodeBhLevel = 2NodeA1hLevel = 1NodeA2hLevel = 1NodeB1hLevel = 1NodeB2hLevel = 1

A hierarchy with a single node

Say the HierarchyTreeInclHeaders range looks like this:

Level1
TopNode

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 1
    properties: { }

hierarchy_levels:
  - level: 1
    name: Level1

hierarchy_inheritance: [ ]

The following hierarchy will be available in the model:

TopNodehLevel = 1
TopNodehLevel = 1

A hierarchy with properties

Say the HierarchyTreeInclHeaders and HierarchyPropertiesInclHeaders ranges look like this:

Level3 Level2 Level1 HierarchyProperties → Prop1 Prop2 Prop3 Prop4
TopNode NodeA NodeA1 1 5 9 13
TopNode NodeA NodeA2 2 6 10 14
TopNode NodeB NodeB1 3 7 11 15
TopNode NodeB NodeB2 4 8 12 16

And the HierarchyPropertiesSettings table looks like this:

Name ResolveMulti
Prop1 FirstOccurrence
Prop2 Mean
Prop3 Error
Prop4 Max

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 3
    properties:
      Prop1: "1"
      Prop2: "6.5"
      Prop3: ""
      Prop4: "16"
  - name: NodeA
    uuid: NodeA
    level: 2
    properties:
      Prop1: "1"
      Prop2: "5.5"
      Prop3: ""
      Prop4: "14"
  - name: NodeB
    uuid: NodeB
    level: 2
    properties:
      Prop1: "3"
      Prop2: "7.5"
      Prop3: ""
      Prop4: "16"
  - name: NodeA1
    uuid: NodeA1
    level: 1
    properties:
      Prop1: "1"
      Prop2: "5"
      Prop3: "9"
      Prop4: "13"
  - name: NodeA2
    uuid: NodeA2
    level: 1
    properties:
      Prop1: "2"
      Prop2: "6"
      Prop3: "10"
      Prop4: "14"
  - name: NodeB1
    uuid: NodeB1
    level: 1
    properties:
      Prop1: "3"
      Prop2: "7"
      Prop3: "11"
      Prop4: "15"
  - name: NodeB2
    uuid: NodeB2
    level: 1
    properties:
      Prop1: "4"
      Prop2: "8"
      Prop3: "12"
      Prop4: "16"

hierarchy_levels:
  - level: 3
    name: Level3
  - level: 2
    name: Level2
  - level: 1
    name: Level1

hierarchy_inheritance:
  - parent: TopNode
    child: NodeA
  - parent: TopNode
    child: NodeB
  - parent: NodeA
    child: NodeA1
  - parent: NodeA
    child: NodeA2
  - parent: NodeB
    child: NodeB1
  - parent: NodeB
    child: NodeB2

The following hierarchy will be available in the model:

TopNodehLevel = 3Prop1 = 1Prop2 = 6.5Prop3 =Prop4 = 16NodeAhLevel = 2Prop1 = 1Prop2 = 5.5Prop3 =Prop4 = 14NodeBhLevel = 2Prop1 = 3Prop2 = 7.5Prop3 =Prop4 = 16NodeA1hLevel = 1Prop1 = 1Prop2 = 5Prop3 = 9Prop4 = 13NodeA2hLevel = 1Prop1 = 2Prop2 = 6Prop3 = 10Prop4 = 14NodeB1hLevel = 1Prop1 = 3Prop2 = 7Prop3 = 11Prop4 = 15NodeB2hLevel = 1Prop1 = 4Prop2 = 8Prop3 = 12Prop4 = 16
TopNodehLevel = 3Prop1 = 1Prop2 = 6.5Prop3 =Prop4 = 16NodeAhLevel = 2Prop1 = 1Prop2 = 5.5Prop3 =Prop4 = 14NodeBhLevel = 2Prop1 = 3Prop2 = 7.5Prop3 =Prop4 = 16NodeA1hLevel = 1Prop1 = 1Prop2 = 5Prop3 = 9Prop4 = 13NodeA2hLevel = 1Prop1 = 2Prop2 = 6Prop3 = 10Prop4 = 14NodeB1hLevel = 1Prop1 = 3Prop2 = 7Prop3 = 11Prop4 = 15NodeB2hLevel = 1Prop1 = 4Prop2 = 8Prop3 = 12Prop4 = 16

An unbalanced hierarchy

Say the HierarchyTreeInclHeaders range looks like this:

Level3 Level2 Level1
TopNode NodeA NodeA1
TopNode NodeA NodeA2
TopNode NodeB
TopNode NodeB

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 3
    properties: { }
  - name: NodeA
    uuid: NodeA
    level: 2
    properties: { }
  - name: NodeB
    uuid: NodeB
    level: 2
    properties: { }
  - name: NodeA1
    uuid: NodeA1
    level: 1
    properties: { }
  - name: NodeA2
    uuid: NodeA2
    level: 1
    properties: { }

hierarchy_levels:
  - level: 3
    name: Level3
  - level: 2
    name: Level2
  - level: 1
    name: Level1

hierarchy_inheritance:
  - parent: TopNode
    child: NodeA
  - parent: TopNode
    child: NodeB
  - parent: NodeA
    child: NodeA1
  - parent: NodeA
    child: NodeA2

The following hierarchy will be available in the model:

TopNodehLevel = 3NodeAhLevel = 2NodeBhLevel = 2NodeA1hLevel = 1NodeA2hLevel = 1
TopNodehLevel = 3NodeAhLevel = 2NodeBhLevel = 2NodeA1hLevel = 1NodeA2hLevel = 1

A hierarchy with empty or missing properties

Say the HierarchyTreeInclHeaders and HierarchyPropertiesInclHeaders ranges look like this:

Level3 Level2 HierarchyProperties → Prop1 Prop2 Prop3 Prop4
TopNode NodeA 1 9
TopNode NodeA 6 10
TopNode NodeB 3 7 15
TopNode NodeB 16

And the HierarchyPropertiesSettings table looks like this:

Name ResolveMulti
Prop1 Mean
Prop2 Mean
Prop3 Mean
Prop4 Mean

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 2
    properties:
      Prop1: ""
      Prop2: ""
      Prop3: ""
      Prop4: ""
  - name: NodeA
    uuid: NodeA
    level: 1
    properties:
      Prop1: ""
      Prop2: ""
      Prop3: "9.5"
      Prop4: ""
  - name: NodeB
    uuid: NodeB
    level: 1
    properties:
      Prop1: ""
      Prop2: ""
      Prop3: ""
      Prop4: "15.5"

hierarchy_levels:
  - level: 2
    name: Level2
  - level: 1
    name: Level1

hierarchy_inheritance:
  - parent: TopNode
    child: NodeA
  - parent: TopNode
    child: NodeB

The following hierarchy will be available in the model:

TopNodehLevel = 2Prop1 =Prop2 =Prop3 =Prop4 =NodeAhLevel = 1Prop1 =Prop2 =Prop3 = 9.5Prop4 =NodeBhLevel = 1Prop1 =Prop2 =Prop3 =Prop4 = 15.5
TopNodehLevel = 2Prop1 =Prop2 =Prop3 =Prop4 =NodeAhLevel = 1Prop1 =Prop2 =Prop3 = 9.5Prop4 =NodeBhLevel = 1Prop1 =Prop2 =Prop3 =Prop4 = 15.5