Use HIERARCHYLEVEL to find a coordinate's position in a given list. Given a value in a line item, this function uses its coordinate to identify its level in the specified list. This can help you determine how far the item is from the root (top-level ancestor) or from the furthest leaf (bottom-level descendant) in the list.
Syntax
HIERARCHYLEVEL(List[, Direction[, Level type]])
Arguments
| Argument | Data type | Description |
| List (required) | List or Time | The name of the list or Time in which the coordinate'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
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
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(Geographic hierarchy)
For each value of the line item, this function returns the number of ancestors of the coordinate of the value in the Geographic hierarchy hierarchy, including the coordinate itself in the count.
HIERARCHYLEVEL(Geographic hierarchy, LEAF, ALL)
This function returns the distance to the furthest leaf descendant of the coordinate within the given list Geographic hierarchy, plus itself, including ALL the list items in the count.
Additional information
The function's result depends on the type of list and the coordinate's position within it:
- If the coordinate isn't part of the specified list, the result is 0.
- Returns 0 if,
- In the
ROOTdirection, the coordinate doesn't have a root ancestor. - In the
LEAFdirection, the coordinate doesn't have any leaf descendants.
- In the
- 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 useslistBas its dimension, you can’t use this function withlistA, iflistAis a parent oflistB.
Examples
The following is an example of a Geographic hierarchy:
| Spain | ||||
| Italy | ||||
| England | ||||
| Scotland | ||||
| Wales | ||||
| Greater London | ||||
| United Kingdom | ||||
| France | ||||
| Germany | ||||
| Europe | ||||
| India | ||||
| China | ||||
| Japan | ||||
| Asia | ||||
| San Jose | ||||
| San Francisco | ||||
| Los Angeles | ||||
| California | ||||
| Texas | ||||
| New York | ||||
| United States | ||||
| Canada | ||||
| North America | ||||
| All regions |
All regions has three children: North America, Asia, and Europe.
North Americahas two children:CanadaandUnited States.United Stateshas three children:New York,Texas, andCalifornia.Californiahas three children:Los Angeles,San Francisco, andSan Jose.
Asiahas three children:Japan,China, andIndia.Europehas five children:Germany,France,United Kingdom,Italy, andSpain.United Kingdomhas four children:Greater London,Wales,Scotland, andEngland.
The following module has three line items:
Itemcontains all the list items fromGeographic hierarchy.Result 1,Result 2,Result 3, andResult 4have theHIERARCHYLEVELformulas.
| Item | Result 1 | Result 2 | Result 3 | Result 4 |
| Spain | 3 | 1 | 2 | 3 |
| Italy | 3 | 1 | 2 | 3 |
| England | 4 | 1 | 2 | 4 |
| Scotland | 4 | 1 | 2 | 4 |
| Wales | 4 | 1 | 2 | 4 |
| Greater London | 4 | 1 | 2 | 4 |
| United Kingdom | 3 | 2 | 2 | 3 |
| France | 3 | 1 | 2 | 3 |
| Germany | 3 | 1 | 2 | 3 |
| Europe | 2 | 3 | 2 | 2 |
| India | 3 | 1 | 2 | 3 |
| China | 3 | 1 | 2 | 3 |
| Japan | 3 | 1 | 2 | 3 |
| Asia | 2 | 2 | 2 | 2 |
| San Jose | 5 | 1 | 2 | 5 |
| San Francisco | 5 | 1 | 2 | 5 |
| Los Angeles | 5 | 1 | 2 | 5 |
| California | 4 | 2 | 2 | 4 |
| Texas | 4 | 1 | 2 | 4 |
| New York | 4 | 1 | 2 | 4 |
| United States | 3 | 3 | 2 | 3 |
| Canada | 3 | 1 | 2 | 3 |
| North America | 2 | 4 | 2 | 2 |
| All regions | 1 | 5 | 1 | 1 |
