1. Row-level and aggregate calculations
Welcome back. In this video, we'll learn how to create calculations in Sigma.
2. Calculations in Sigma
Now that we've brought the data into our Sigma workbook, let's start putting it to use.
One of the first things the Oakmark Bank team might ask us to find out is how long each call lasted in minutes. To figure that out, we'll write a simple calculation or function so Sigma can do the math for us.
3. Calculations in Sigma
However, once we know how long each call was, they might also want to know the average call length by day or agent. Or maybe the grand total of minutes for those same groupings.
4. Groupings
Grouping data categorically, also known as dimensions, helps us answer "For each" questions in our data. For example, for each day, what was the average call length, or for each agent, how many minutes did they spend on calls on any given day?
5. Using the Formula Editor
Sigma's spreadsheet-like interface makes it easy to answer all these questions in a single data element.
Just like a spreadsheet, you can write formulas that reference other columns, combine values, or apply logic.
For example, calculate the total time the caller was on the phone by calculating the minutes between the call received and the call end time, and then use an if statement to identify calls over or under the recommended time to resolution.
Since these formulas return one result per row, they are called row-level calculations.
6. Aggregating table data with Groupings
But what if we want to summarize the data to a higher level or category? That's where table-level groupings come in.
Groupings let us change the granularity of the analysis by organizing the data into categories like call records by day or agent.
Then we can calculate things like the total number of minutes or the average call length for each group.
7. Aggregating table data with Groupings
What makes Sigma so unique is that we can build these multiple levels of aggregation within the same table and use and reuse calculations at each level.
For example, our row-level calculation finds how long a caller has been on hold, and the grouping shows the average hold time per team. This makes it easy to compare performance, like spotting which team is understaffed.
8. Let's practice!
Let's first take a closer look at creating row-level calculations, then dive into how groupings work.