The ISANCESTOR function takes two list or time period values. It returns TRUE if the first is an ancestor of the second. Ancestors are an item's parent, parent's parent, and so on.
Syntax
ISANCESTOR(Ancestor, Descendant)
Arguments
| Argument | Data type | Description |
| Ancestor | List, time period | The value to test whether it is an ancestor of the Descendant argument. |
| Descendant | List, time period | The value to test whether it is a descendant of the Ancestor argument. |
The ISANCESTOR function returns a Boolean result.
Calculation engine functionality differences
In Polaris, the ISANCESTOR function returns FALSE when given a top-level item coupled with an orphan entity.
In the Classic Engine, this returns TRUE.
Examples
Identifying parent products
In this example, a module has the Products list on columns, and the following line items on rows:
- Product, a List data type on the Products list
- Is Fruits parent?, a Boolean data type
- Is Vegetables parent?, a Boolean data type
- Is Fresh produce parent?, a Boolean data type
The Products list is a hierarchical list. Within this list, Fresh produce are the parents of Fruits and Vegetables.
Following is the Products list as displayed under Grid View.
| Parent | Code | |
| Apple | Fruits | |
| Orange | Fruits | |
| Banana | Fruits | |
| Fruits | Fresh produce | |
| Carrot | Vegetables | |
| Beetroot | Vegetables | |
| Broccoli | Vegetables | |
| Vegetables | Fresh produce | |
| Fresh produce |
| Apple | Orange | Banana | Fruits | Carrot | Beetroot | Broccoli | Vegetables | Fresh produce | |
Product
| Apple | Orange | Banana | Carrot | Beetroot | Broccoli | |||
Is Fruits parent?
| true | true | true | ||||||
Is Vegetables parent?
| true | true | true | ||||||
Is Fresh produce parent?
| true | true | true | true | true | true |
The first line item, Product, returns each product within the Products list. Three further line items use the ISANCESTOR() formula to check whether these products are part of Fruits, Vegetables, or Fresh products, respectively.
Identifying organizational hierarchy
In this example, a module has the Organization list on columns and line items on rows.
The Organization list is a hierarchical list with a top level of company. Within this list, regions are the parents of countries, and countries are the parents of cities.
The first line item, City, uses the ITEM function to return each location within the Organization list. Three further line items use the ISANCESTOR function to check whether these cities are part of the UK, France, or the EMEA region, respectively.
| London | Birmingham | Paris | Lyon | New York | Los Angeles | |
| City | London | Birmingham | Paris | Lyon | New York | Los Angeles |
Part of UK?
| true | true | ||||
Part of France?
| true | true | ||||
Part of EMEA?
| true | true | true | true |
Time period example
In this example, the Dates list is on columns, and line items on rows. The Chosen Month and Chosen Quarter line items have the time period data type. The Quarter is Ancestor? has the Boolean data type and uses the ISANCESTOR function. This returns a Boolean value of TRUE if the value of Chosen Quarter is an ancestor of Chosen Month.
The final line item, Within Q3?, uses a direct reference to the time period of Q3 FY22 and checks whether it's the ancestor of Chosen Month.
| Key Dates | Expiry Dates | |
| Chosen Month | Feb 2022 | Jul 2022 |
| Chosen Quarter | Q1 FY22 | Q1 FY22 |
Quarter is Ancestor?
| true | |
Chosen Month Within Q3 FY22?
| true |
