Use ITEMLEVEL to find a given item's position within its list. The list is identified based on the given item's data type. You can use this function to find the distance from the item to either its root ancestor or its furthest leaf descendant.
Syntax
ITEMLEVEL(Item[, Direction])
Arguments
| Argument | Data type | Description |
| Item | List item | A list item whose distance to either the root ancestor or the most distant leaf descendant is to be returned. |
| Direction (optional) | Keyword | Determines the direction in which the distance is measured. The keywords are
|
The ITEMLEVEL function returns a numeric value.
Calculation engine functionality differences
This function is only available in the Polaris Calculation Engine.
Syntax example
ITEMLEVEL(United States)
The item United States belongs to a list called Geographic hierarchy. The function returns the number of items in the list from United States to its root ancestor, including the item itself in the count.
ITEMLEVEL(United States, LEAF)
This function returns the number of items in the Geographic hierarchy list from United States down to its furthest descendant, including the item itself in the count.
Additional information
The function's result depends on the type of list and the list item's position within it. Key points include:
- The function counts only the items in the list. The list is identified based on the item's data type.
- Returns 0 if,
- In the
ROOTdirection, Item doesn't have a root ancestor. - In the
LEAFdirection, Item doesn't have any leaf descendants.
- In the
- If the list item is blank, the result is 0.
- Top-level items are treated as the (unique) root of the corresponding lists.
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 1andResult 2have theITEMLEVELformulas.
| Item | Result 1 | Result 2 |
| Spain | 3 | 1 |
| Italy | 3 | 1 |
| England | 4 | 1 |
| Scotland | 4 | 1 |
| Wales | 4 | 1 |
| Greater London | 4 | 1 |
| United Kingdom | 0 | 0 |
| France | 3 | 1 |
| Germany | 3 | 1 |
| Europe | 0 | 0 |
| India | 3 | 1 |
| China | 3 | 1 |
| Japan | 3 | 1 |
| Asia | 0 | 0 |
| San Jose | 5 | 1 |
| San Francisco | 5 | 1 |
| Los Angeles | 5 | 1 |
| California | 0 | 0 |
| Texas | 4 | 1 |
| New York | 4 | 1 |
| United States | 0 | 0 |
| Canada | 3 | 1 |
| North America | 0 | 0 |
| All regions | 0 | 0 |
ITEMLEVEL(Item)counts the number of root ancestors the Item has, plus itself.ITEMLEVEL(Item, LEAF)counts the number of items in the path to the Item's furthest descendant, plus itself.
