Use HIERARCHYLEVEL to determine the position of the coordinate of a line item's point in a given list. You can use this function to find the distance to either the root ancestor or the furthest leaf descendant of the list items within a given list.

Syntax

HIERARCHYLEVEL(List [, Direction [, Level type]])

Arguments

ArgumentData typeDescription
List (required)List or Time

The name of the list or a reference to a time period from where the point's distance to either the root ancestor or the most distant leaf descendant is to be returned.

The list you provide must be related to one of the dimensions used by the line item on which the formula is set.

Direction (optional)Keyword

Determines the direction in which the distance is measured.

The keywords are ROOT and LEAF:

  • ROOT - Counts how many ancestors the point has, plus itself.
    This is the default keyword if you omit the Direction argument.
  • LEAF - Counts the number of items in the path to the point's furthest descendant, plus itself.

This argument is optional. But if you want to use the Level type, you must provide this argument.

Level type (optional)Keyword

Determines which list items to include in the count.

The keywords are ALL and EXPLICIT:

  • ALL - Includes all list items to the count.
    This is the default keyword if you omit the Level type argument.
  • EXPLICIT - Includes only one item for each distinct list that makes up the hierarchy.

This argument is optional. But if you want to use this argument, you must provide the Direction argument.

The HIERARCHYLEVEL function returns a numeric value.

Calculation engine functionality differences

This function is only available in the Polaris Calculation Engine.

Syntax example

  HIERARCHYLEVEL(Regions)


This function returns the distance to the root ancestor of the coordinate of the point corresponding to the given list Regions, including itself.

  HIERARCHYLEVEL(Regions, LEAF, ALL)


This function returns the distance to the furthest leaf descendant of the coordinate of the point within the given list Regions, plus itself, including ALL the list items to the count.

Additional information

The function's result depends on the type of list and the point's position within it:

  • If the point coordinate isn't part of the specified list, the result is 0.
  • Returns 0 if,
    • In the ROOT direction, point doesn't have a root ancestor.
    • In the LEAF direction, point doesn't have any leaf descendants.
  • Top-level items are treated as the (unique) root of the corresponding lists.

Constraints

  • The list you provide must be related to one of the dimensions used by the line item on which the formula is set.
  • It also won’t work with ancestor hierarchies of the target dimension.
    For example, if your line item uses listB as its dimension, you can’t use this function with listA,  if listA is a parent of listB.

ITEMLEVEL

Examples

Consider the following:

  • A simple list, Regions, with a top level All Regions
All RegionsAmericas



North America


South America



Brazil

Europe

  • A composite list, Subregions, with parent Regions

ParentCode
São PauloBrazil
Rio de JaneiroBrazil
Western EuropeEurope

The below module is dimensioned by Subregions. Result 1, Result 2, Result 3, and Result 4 are four line items that use the HIERARCHYLEVEL formulas and are set to Formula summary method.


North AmericaSão PauloRio de JaneiroBrazilSouth AmericaAmericasWestern EuropeEuropeAll Regions

Result 1

  HIERARCHYLEVEL(Subregions, ROOT, ALL)

355432321

Result 2

  HIERARCHYLEVEL(Subregions, ROOT, EXPLICIT)

233222321

Result 3

  HIERARCHYLEVEL(Subregions, LEAF, ALL)

011234125

Result 4

  HIERARCHYLEVEL(Subregions, LEAF, EXPLICIT)

011222123