Hierarchies (beta)
This feature is currently in Beta and subject to quick, iterative changes. As a result, the latest product version may differ from the contents of this document.
Sigma has a hierarchy feature that you can use for grouping the data and calculations at various category levels, and to keep track of the order between the coarsest and finest granularity of data. The hierarchies can be inherited between all parent and children elements; they can only be used inside pivot tables.
Common examples of hierarchies would be for Product (type, family, name), Location (continent, country, region, state, city), Time (year, month, week, day), Taxonomy (kingdom, phylum, class, order, family, genus, species), and so on.
Contents
Create a hierarchy
Follow these steps to create a hierarchy in your worksheet's pivot table:
- In your workbook, switch to the Edit mode.
- Select the element where you want to create hierarchies.
You can create a hierarchy in a table or in a pivot table.
Keep in mind that you can only use hierarchies in pivot tables. - In the Columns menu, click the + icon, and choose Manage hierarchies.
- The Manage hierarchies interface appears.
- Click + New hierarchy, and define hierarchy properties.
Here, we define the Product hierarchy.
- Hierarchy name
- Provide a title of your hierarchy.
-
- Example
- Product
- Default
- Hierarchy
- Columns in hierarchy
- Click the + icon, and select/add the columns Product Type, Product Family, and Product Name.
- Note that after you add a column to any hierarchy, this column has a hierarchy symbol icon,
.
The hover over describes the hierarchy where the column appears. - To create additional hierarchies, repeat the steps.
Here we define the Metrics hierarchy, with the columns Cost and Price.
Manage hierarchies
You can change existing hierarchies by changing the Hierarchy name, reordering columns, adding new columns, removing existing columns, or removing the hierarchy.
To change the definition of an existing hierarchy, follow these steps:
- In the Manage hierarchies interface, select the hierarchy you plan to change.
- Click the + (plus) icon to add new columns.
- Click the x (remove) icon next to the column name to remove it from a hierarchy.
- Click the x (remove) icon next to the hierarchy name to delete it.
Hierarchy inheritance
Any child of an element that has a hierarchy inherits that hierarchy. However, you can use hierarchies only in pivot tables.
For example, you can create a new pivot table as a child element of the table or pivot table where you defined hierarchies; click the (child) icon. This new pivot table has its parent's hierarchies by default. You cannot change or remove hierarchies that the parent pivot table defines.
However, you can create a new hierarchy in the child element, and have full editing privileges there.
Using hierarchies
To use a defined hierarchy as a pivot, follow these steps:
- To create a pivot row, click the + (plus) icon next to Pivot Rows.
- From the Add new column menu, select a hierarchy.
Here, we selected the Product hierarchy. - To use a hierarchy in values, click the + (plus) icon next to Values.
- From the Add new column menu, select a hierarchy.
Here, we selected the Metrics hierarchy. - Note that the New Pivot Table uses the Product hierarchy and the Metrics hierarchy.
Limitations
- You can use hierarchies in pivot tables only, not in other other elements.
- Hierarchies exist on workbooks, and cannot be passed between other objects such as datasets, tables, or other workbooks.
- When you update the hierarchy, the app does not update the hierarchy where the hierarchy is used.