Grouping tables are a best practice in Sigma Computing because they provide a lot of flexibility in terms of the calculations that can be performed, as well as the aggregation levels of those calculations.
In this blog, we will explore the significance of the level of aggregation in data. We will examine how the level of aggregation of a grouping table can result in different outcomes for their child elements, and demonstrate how to modify the aggregation level of child elements in order to produce accurate portrayals of the data.
What are Grouped Table Aggregation Levels, and Why Does it Matter?
Sigma table elements are elements that exist within the workbook they were created in, not to be confused with tables from a database. Table elements in Sigma are incredibly flexible, allowing users to transform their data to fit their needs.Â
Sigma table elements offer the ability to organize and compare data through the use of grouped columns and grouped calculations. By selecting a specific column as a grouping key, the rows of data within the table element are merged into cells based on their unique values in that column.Â
The rows to the right of the grouping key are then divided into groups according to these unique values. Aggregate calculations can be created and performed on the cell values within each group.Â
The following grouped table element is grouped by Order Number as shown in blue. It has an aggregate calculation Order Sale that sums up the Sale values for each Order Number. The red values display the rows of data that fall within each Order Number.
This functionality provides a useful tool for analyzing the data in the table element, whether in the table element itself or in a child element that is created off of it.
Within grouping table elements, the plus and minus buttons on the grouped column headers enable users to visually understand the drill-down path from the broadest to the most granular level of your data. These levels of detail set by your custom groupings affect their child elements as well.
For this blog, we will use the Plugs Electronics data as an example. As shown below, we have grouped Store Region and Order Number, and have created a grouped calculation for both groupings, the sum of sales, to calculate Region Sale and Order Sale.Â
While this table still has all the same data regardless of its aggregation level (how it is expanded or collapsed), it provides a different view to the user. The current aggregation level of the table also impacts what data its child element inherits.Â
The images below show this grouping table at different levels of aggregation, as well as the child element that is created.Â
It is important to note that after the child element is created off the base table, changing the level of aggregation at the base table will not impact its child elements. The state of the base table as the child element is created is what determines the data in the child element.
Why Does the Expanded/Collapsed Aggregation Level Matter?Â
- The aggregation level will affect which columns you have access to using initially.
- If the base table is fully collapsed when the child element is created, the child element will not inherit the Order Sales column or any other granular column such as Date or Product Type.
- The aggregation level will impact how you aggregate your values in your child elements initially.
- Based on the level of aggregation, columns may or may not have repeated values as shown in Child Element 2 and Child Element 3, which will impact how these values should be aggregated in child elements.
Continuing with my example, I’ve created the same visualization, but both were created based on different aggregation levels. In order to create the same visual, I had to aggregate the data within the visuals in different ways.
This child element visualization was created off a fully collapsed grouping table (Base Table 1). Because of the aggregation level, the Region Sales did not need to be aggregated in the visual.
This child element visualization was created off of a partially collapsed base table (Base Table 2). Because of the aggregation level, Region Sale needed to be aggregated in the visual to produce accurate results. Since all the Region Sale values within each Region are the same, we could aggregate them as averages, minimums, or maximums to pull out the Region Sale values.Â
As shown above, aggregation levels matter, and without understanding them and how they affect your data, mistakes can easily be made.Â
If you know what data you will need in your child element, you can set the base table’s aggregation level accordingly before creating the child element off of it. There might be instances, however, where you forget to alter the aggregation level or you might change your mind about what level you need for your child element.Â
Have no fear, in this case, Sigma has a feature that enables you to alter the aggregation level of your child element from the child element itself
How to Change the Level of Aggregation
In this example, we have a blank visualization child element that was created off of Base Table 2. We want our visual to show Region Sale over time, however, the current aggregation level does not contain the Date column. To use the Date column, we need to change the aggregation level of our child element while in Edit mode.
The quick steps below walk through this process.
1. Click Onto the Child Visualization ElementÂ
- A border with your name on it will surround the child element, and the element properties panel will appear on the left.
2. Select Your Aggregation Level of Choice at the Bottom of the Element Properties Pane
- There will be one option for each aggregation level (grouped level of your parent table). In this example, there are three aggregation levels available, which match up to the data in Child Element 1, Child Element 2, and Child Element 3. To get the Date column, we will select the All source columns option. The All source columns option refers to the lowest level of granularity that exists in your source table, where there are no aggregations.Â
3. Select the ^ Button to View Available Columns, and Select Newly Available Columns
- If you’re setting the aggregation level lower (more granular), more columns will become available to you, but they will be un-selected by default. Click the checkbox to select the additional columns to gain access to them in the visualization. In our example, we select the Date column. When we exit the aggregation level window, we now have access to the Date column and can use it in our child element visualization.
Why Even Use Aggregation Levels?
Yes, aggregation levels can trip you up if you’re not conscious of how to use them, however, when fully utilized their flexibility is unmatched. Whether you need a particular grouped calculation or you need the most granular data, utilizing aggregation levels in your grouped tables enables you to use your table element in a plethora of scenarios.Â
This, in turn, will also reduce the number of table elements you need in your workbook, making for a more manageable workbook lineage, which refers to the inheritance and relationships between data elements in a workbook.Â
Closing
Grouping tables in Sigma enables users to perform various types of analysis through a range of options for aggregating and calculating data. The aggregation level of these tables affects the columns available to its child elements, as well as how the resulting values in the child element must be aggregated.Â
The state of the base table is what first determines the aggregation level of the child element, however, it can easily be adjusted in a few steps. As demonstrated, it is crucial to understand how aggregation levels work as they can impact the data and cause errors if not considered.Â
However, once the concept is understood, it is straightforward to adjust the aggregation level of child elements to meet the needs of the analysis and make the most use of your data.
Need Help With Sigma?
phData, the largest consulting provider for Sigma, has a team of expert consultants who can offer guidance and assistance in your Sigma journey.
Looking for additional information about Aggregations? Check out this Mastering Aggregations video from our Sigma Made Simple Video Series.
FAQ's
If the columns exist in your grouped source element but do not show up as available columns in its child element, the aggregation level is likely set to a higher level of aggregation. Lower the level of aggregation in your child element, and select the newly available columns to gain access to those columns in your child element.Â
When your child element is set to a lower level of aggregation than the grouped columns and grouped calculations, the grouped column and grouped calculation values repeat in your table in order to show the set level of aggregation (as shown in Child Element 1). Either change the level of aggregation to the highest level you would like to utilize, or aggregate your grouped columns and grouped calculations using Min(), Max(), or Avg() in order to pull an accurate value.Â
