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:

  1. In your workbook, switch to the Edit mode.
    editing option
  2. 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.
  3. In the Columns menu, click the + icon, and choose Manage hierarchies.
    How to get to Manage hierarchies interface
  4. The Manage hierarchies interface appears.
    Blank Manage hierarchies interface
  5. 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.
    The Product hierarchy
  6. 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.
    Columns tagged as part of a hierarchy
  7. To create additional hierarchies, repeat the steps.

    Here we define the Metrics hierarchy, with the columns Cost and Price.

    The Metrics hierarchy

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:

  1. In the Manage hierarchies interface, select the hierarchy you plan to change.
  2. Click the + (plus) icon to add new columns.
    Add a column to an existing hierarchy
  3. Click the x (remove) icon next to the column name to remove it from a hierarchy.
    Remove a column from a hierarchy
  4. Click the x (remove) icon next to the hierarchy name to delete it.
    Delete a hierarchy

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. 

The Manage hierarchies interface shows non-operable hierarchies that are defined on the parent pivot table

However, you can create a new hierarchy in the child element, and have full editing privileges there.

The Manage hierarchies interface shows you can add a new hierarch to the child pivot table

Using hierarchies

To use a defined hierarchy as a pivot, follow these steps:

  1. To create a pivot row, click the + (plus) icon next to Pivot Rows.
  2. From the Add new column menu, select a hierarchy.
    Here, we selected the Product hierarchy.
    Defining a pivot row through a hierarchy
  3. To use a hierarchy in values, click the + (plus) icon next to Values.
  4. From the Add new column menu, select a hierarchy.
    Here, we selected the Metrics hierarchy.
    Defining the values of a pivot table through a hierarchy
  5. Note that the New Pivot Table uses the Product hierarchy and the Metrics hierarchy.
    Pivot table with hierarchies

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.

 


Was this page helpful?
Yes No