Levels and groups are a way to organize and analyze your data. Often, we want to create groups within the data based on common characteristics. Maybe you want to see all the new sign-ups in a day or all of the merchandise in a department. Groups and levels are a way to accomplish that. To visualize how they work, let’s go back to the basics.
Think of a collection of blocks, all different colors and shapes, all piled together. This represents our data.
In Sigma, each individual block is represented by a row. Properties of the block, like color and shape, are listed in columns.
All of the column names in Sigma are listed in the worksheet's right-side panel, where we show you how the data is being organized. This is also where you can create groups and levels. Levels and groups allow you to very quickly surface information about groups that is hard to see otherwise. Let’s look at an example:
How many blocks are blue?
Let’s say we want to know how many blocks are blue. When there are only a few shapes, you may be able to just count them. When there are more, it’s much easier to group the blocks together by color first. Actual toy blocks can be stacked into color-specific piles and counted.
In Sigma, you can create a new level that groups your blocks by color. This is very similar to sorting out the blocks into different piles.
In the image below, our grouped column, COLOR, is displayed under LEVEL 1 in the worksheet's right-side panel. It's green-grey background indicates that the column is acting as a grouping key.
A grouping key determines which distinct groups exist in a level and which of those groups each individual row (our blocks) fits into.
In our blocks example, the COLOR column has 3 distinct values: blue, green and yellow. Every row represents a block and every block has one of those three colors. As such, each row is grouped based on its COLOR value.
Now that we have our rows grouped by color, we can add a calculation to find out how many rows are in each group. These numbers will represent the number of blocks of each color.
The Count() calculation will count all the rows in each group on the level it is placed on. With only 24 rows, you could count manually, but with 24 million (or more!) that isn’t an option, making count a very useful function.
By adding a new calculated column "Count by Color" on the level grouped by color, and using the formula Count(), we learn that there are 8 blue blocks.
How many blue squares are there?
Once we answer our initial question, there are often more questions that arise. For example, now that we have our blocks grouped by color, what if we want to know how many blue squares exist? Or how many green triangles? We can group by shape, and apply a count to the rows to answer our questions.
Creating a level underneath our existing level will create new groups, within the existing level. It’s like organizing blocks by color, then sorting by shape within those color groups.
The color groups still exist, we’ve just added groups by shape as well. It’s another level of groups.
In Sigma, it works similarly. You can see the level grouped by COLOR, and then see the next level with the rows grouped by SHAPE. We’ve added a formula to count the rows under both levels so you can see the total number of rows in each color group and the number of rows for each shape and color. The number of rows is the number of blocks because each block is represented by one row. We can see that there are 3 blue squares.
Looking at the right-hand panel, you can see that the group keys for each level are light blue. The "Count by Color" column, and the base columns, have white backgrounds. How many squares are there? How many are blue?
How many squares are there? How many are blue?
Let’s say that after looking at our data, we’ve decided we no longer need to know how many blocks of each color we have, but the number of each shape is important. We want to know the total number of squares, as well as the number of blue squares. How can we find that information?
The quickest way is to change how we have our rows (or blocks) grouped. Rather than grouping the blocks together by color and then organizing them by shape, we can group the blocks by shape, and then by color.
There are still two levels and the same two grouping keys, but the order changes the information we can get. Unlike with blocks, in Sigma changing how we have our rows sorted takes just a few seconds. Rearranging the levels quickly gives us the results we want. We can see that there are 7 squares and 2 blue squares.
Count is just one of many functions you can use to analyze your data. You can quickly Sum all of the values in a group, or take the Average. Check out our function index to learn more about all the the Aggregates that you can apply. Or explore the other functions you can use to create complex analysis at all of the levels of your data.
Understanding the power of groups is the first step in unlocking the power of Sigma. Organizing blocks by hand is fun and playful. With Sigma, organizing millions of rows of live data from your data warehouse can be just as playful. Unleash your curiosity and start asking more questions.