Built-in Functions → descendantOf

descendantOf() is a function that uses a column of a hierarchical table as the first parameter and returns true for each row where the first parameter is a descendant of the second one, down to the specified depth level, otherwise, it returns false.

Note

A hierarchical table with a self-join has two extra columns, LEVEL and IS_LEAF, in a physical schema.

LEVEL is an integer representing an item's level of the hierarchy (0-based indexed), and IS\_LEAF is a boolean indicating true if the item is at the bottom of a particular branch.

Signature

descendantOf(field, value, depth)

The following table illustrates the descendantOf function parameters:

Parameter	Description
field	A column of a hierarchical table (e.g. Employee Name)
value	Can be a value (e.g. Joe), or a variable (e.g. $employee)
depth	An integer to specify the depth level within a hierarchical table

Note

When creating a variable for the second parameter to compare against the records of the first one, you can give it a default value, which users can change on the Insight level, descendantOf(field, $sessionVariable, depth). For example, you can create an internal variable, query(3), name it Emp, and then use it in the descendantOf function as follows: descendantOf(Employee_ID, $Emp, 2). To learn more, review Concepts → Internal Session Variable.

Returns

boolean

Example

Identify the employees that are descendants of John down to depth level 2.

descendantOf(
    HR.EMPLOYEES.FIRST_NAME,
    "John",
    2
)

The following table illustrates the behavior of the descendantOf() function:

FIRST_NAME	Manager	LEVEL	IS_LEAF
John	NULL	0	False
Paul	John	1	True
Mary	Paul	2	True
Dan	Mary	3	False

This example returns true for each record (in the “FIRST_NAME” column) down to depth level 2 that is a descendant of “John”. John is the manager of Paul, Paul is the manager of Mary, and Mary is the manager of Dan. Thus, the function will return true for Paul and Mary only, and false for Dan, since Dan is at depth level 3.

Use the following steps for detailed instructions on how to use the descendantOf() function:

Note

In the Cluster Management Console (CMC), create a tenant that includes Sample Data. The Sample Data includes the HR schema.

In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
In the Add Dashboard dialog, for Name, enter Product Dashboard, and then select Add.
In the Action bar, select + (add icon), or select + Add Insight.
In the Insight panel, select Listing Table or V.
In Tables, select Aggregated Table.
In the Data panel, select Manage Dataset.
In the Manage Data Sets panel, in Tables, select HR. Close the panel.
From the Data panel, drag and drop the following columns from the EMPLOYEES table to the respective tray:
- Employee First Name and Employee Id to the Grouping Dimension tray.
- Employee Salary to the Measure tray.
  - In the Properties panel, for Number Format, select Dollar Rounded.
From the Data panel, drag and drop Add Formula to the Grouping Dimension tray.
The Formula Builder automatically opens:
- In Search Functions, select the down arrow, and then select Miscellaneous Functions.
- Double-click the second descendantOf function, descendantOf(field, value, depth), to add the formula to the editor.
- In the Formula Editor,
  - Replace field with Employee First Name from the Data panel
  - Replace value with “John”
  - Replace depth with 2
- Select Validate & Save.

Content

Signature

Returns

Example