Levels: Unlocking the Power of Sigma
Sigma makes cloud analytics as approachable as a spreadsheet and as powerful and expressive as SQL. Levels are one of the keys to unlocking that power.
Levels are a fundamental concept in Sigma. They are expressed visually in our Worksheets, helping even those unfamiliar with the concept to quickly grasp what is going on. Even those familiar with SQL benefit from the visual interface.
Sigma provides the power to easily create analysis that challenges even experienced SQL programmers.
Creating a Level
Initially our data is flat. It has a single “base” level. Adding additional levels opens up new possibilities for analysis.
Let’s divide our data by Day, so we can start analyzing the data by day rather than by each transaction. We’ll do this by creating a new level, keyed by the Day column.
To add a new level, click the grey and white + sign under “Level 1” in the box labeled “Select Grouping Key”. You can then select the column you want to act as a level key.
You can also drag and drop the column you would like to group by. Simply click and drag the column into place to create a new level.
Notice how every individual volume row is now “grouped” by its day in the table. This visual expression helps new user grasp the utility of levels, and makes examining the row level data very easy.
Creating a Level Hierarchy
We aren’t restricted to just a single level. With Sigma, you can create multi-level hierarchies so you can drill down into your data any way you want. Let’s add an additional level to split the rows by Year.
Click the… next to ‘Level 1’ and select ‘Add Grouping Level’. You can now select ‘Year’ to be the grouping key. You could also drag and drop the year column above Level 1, and a new level will appear.
Our table now shows all the rows in a year (1962) and then within that year, all the rows divided by day.
Computing Values in Levels
Sigma has a Function Index covering the most commonly used operations, including Aggregates.
To create an Aggregate, we create a new column and add a formula.
We’ll add a SUM ([Volume]) formula in the day and year level to calculate the yearly and daily volume of sales.
Our formula is the same, but the level placement determines whether the yearly or daily total volume is displayed.
Our table now shows all of rows in a year, and the total Volume traded in that year. It then shows all of the days in that year, with the rows grouped by day, and the daily volume traded.
Formulas can even cross levels
Formulas can cross levels without anything special being done. Columns from another level can be referenced easily. Let’s calculate what each stock’s volume contributes to the daily total.
What percentage is each stock’s volume of the daily total?
In Sigma, it’s just adding a new column to the base level with the formula [Volume]/[Daily Total]
We now have a column in the base level of the worksheet showing each stocks % contribution to the daily volume.
Collapse / Expand Levels
When we want a different view of the data, we can adjust our view by collapsing or expanding individual levels. This makes it easy to get a high level view of our data.
Let’s collapse down the base level and the Day level and only see the yearly totals. To so that, we just need to click the chevron next to the first column in the level.
Sigma’s visual expression of levels makes the concept easy to grasp for those without SQL background, and they also make complex analysis quicker and easier for SQL experts. Our quick tutorial generated 150 lines of SQL, all through an intuitive visual interface in less time than even an expert SQL developer could manage.
Levels are at the heart of Sigma, and enable everyone to complete complex analysis. Sigma has 99% of the functionality of SQL, all through an intuitive visual interface that is immediately familiar.