Work with hierarchies (Beta)

Use hierarchies to group columns categorically and define the order of data granularity. For example, some common example hierarchies include:

• Product: type, family, name
• Location: continent, country, region, state, city
• Time: year, quarter, month, week, day
• Organization: department, team, role

You might create a hierarchy to simplify development of a profit and loss statement, bill of materials analysis and reporting, inventory analysis, and more.

Creating a hierarchy in Sigma lets you work with a hierarchy like you might a column — instead of a folder which only creates an association of the columns together, a hierarchy lets you do the following:

• Define the order of the hierarchical data.
• Easily visualize all parts of the hierarchy together in a pivot table or grouped table.
• Format all levels of the hierarchy consistently, such as when applying conditional formatting.
• Filter different levels of the hierarchy using a filter on the data element or with a control element. See Filter your table by hierarchy.
• If your hierarchical data is ragged, Sigma visualizes the ragged hierarchy, letting you view your hierarchy in a dense layout instead of empty rows like a non-ragged hierarchical view. See View a ragged hierarchy.

Migrate existing hierarchies to hierarchy columns

🚧

Creating a hierarchy from the Manage hierarchies popover will eventually be deprecated. Sigma will continue to support them until hierarchy columns are fully developed and can facilitate a seamless transition. Prior to the end of support, migrate to hierarchy columns created with the RaggedHierarchy function, as specified in this document (see Create a hierarchy).

If you have an existing hierarchy defined in Manage hierarchies, you can do the following:

• Recreate the hierarchy as a hierarchy column. Follow the steps to create a hierarchy with the same columns that represent your current hierarchy.
• Stop using the hierarchy after support ends.

Limitations

The following limitations apply to hierarchy columns:

• Hierarchies are only fully supported in pivot tables and grouped tables. Charts have limited support, with the first level of a hierarchy being available to reference.
• Hierarchies are not supported in datasets.
• Hierarchies cannot be used with actions.
• Hierarchies cannot be used in input tables.

Create a hierarchy

You can create a hierarchy by selecting columns in your data element, or by using a function. After you create a hierarchy in a data element, any child elements inherit the hierarchy.

From the column list

1. Select the element for which you want to add the hierarchy.
2. In the list of columns, select the columns that you want to add to the hierarchy, then select the down arrow () and choose Create hierarchy....
3. A new hierarchy column is added to your data element, titled New Hierarchy by default.

💡

When you create a hierarchy from the column list, it is formatted as a ragged hierarchy by default.

Rename the hierarchy column to whatever you want and add it to a pivot table or grouped table to visualize the hierarchy levels.

With a function

When you create a hierarchy from the column list, Sigma creates a new column that uses the RaggedHierarchy() function. You can also create a calculated column that uses RaggedHierarchy() function directly.

Creating a hierarchy with a function

1. Add a calculated column.
2. In the formula bar, add the RaggedHierarchy function, then add each column in the hierarchy in order from highest-level to lowest-level. For example:

RaggedHierarchy([Product Type], [Product Family], [Product Line])

For more information, see the function documentation for RaggedHierarchy.

Add a hierarchy to a pivot table or grouped table

To visualize a hierarchy in Sigma, add the hierarchy column to pivot table rows, pivot table columns, or a table grouping level by dragging and dropping the column from the column list.

For example, to add a hierarchy to a pivot table:

1. Open the workbook for editing or customize the view.
2. Select the pivot table element.
3. In the editor panel, for pivot rows, select + (Add column).
4. Select the hierarchy column. For example, Product Hierarchy.

View a ragged hierarchy

If your data is ragged, and therefore does not have a value for every level of the hierarchy, Sigma filters out the null values to display them in a compact, ragged view.

For example, given a Location hierarchy defined of Region, State, and City columns with values like the following:

When viewed in a pivot table or grouped table as the Location Hierarchy, the values appear as if only the non-null values are present:

Modify a hierarchy

After you create a hierarchy column, you can edit the column order of your hierarchy.

Change hierarchy columns

After you create a hierarchy column, you can change the order of columns in the hierarchy or which columns are included in the hierarchy:

1. While editing or customizing the workbook, locate the element with the hierarchy defined.
2. Select the element and select the hierarchy column.
3. In the formula bar, modify the RaggedHierarchy() function definition to include different columns or change the order.

Convert your hierarchy to a ragged or non-ragged hierarchy

1. Select the hierarchy column, then select the down arrow ().
2. Select Transform, then select Convert to ragged or Convert to non-ragged.

Filter your table by hierarchy

If you want to filter your pivot table or grouped table by specific values in your hierarchy, add a filter that targets the hierarchy column. You can choose values from any level of the hierarchy to include or exclude.

📘

If your hierarchy contains thousands of distinct values, the filter might not load as expected. Consider filtering your data to fewer rows before using the hierarchy filter.

You can also convert your filter to a control. See Hierarchy in the Intro to control elements.