DISCLAIMER: This is a Sigma sponsored post
Welcome to my first blog showcasing how to build analytics in Sigma. I’ve been using Sigma for the past year or so and I have to say, I’ve been quite impressed with everything it can do. In this blog, I’m going to show you how to create an analysis showcasing YoY order frequency. This dashboard is inspired by a previous Workout Wednesday challenge. You can check out that challenge here
Shaping the data
You can see from the example above we going to be creating a line chart where each line is a year of sales with day of year on the x-axis and % of Total Orders on the y-axis
So for this analysis we’re going to use the Plugs Retail data model that’s available in the Sigma Sample Database. We only need the last 4 years and the only fields we need for this analysis are:
- Order Number
- Order date
- Product Hierachy
- Type
- Family
- Line
- Group
Once we have our table, we’ll use this as our parent table for the rest of our analysis. Let’s create a child table off of this parent table and rename it Year and Day of Year Group.
The first thing to do is add a grouping for year of order date and we’ll aggregate the unique count of order number as a grouped calculation (this will be our denominator in our final calculation later). We also need an additional grouping for each date and the order count. Your groupings and table should look something this
We also need to create a new dimension to return the day of the year for each date since that is going on the x-axis. Thankfully Sigma has an OOTB (out of the box) function to calculate the day of year
DayOfYear([Day of Order Date (Sales)])We’ll add this as an additional dimension in the day of date grouping and just like that we have what we need to generate our x-axis as well as our 4 lines! We just need to calculate the y-axis. To do that we need to create a running sum of the daily orders. Thankfully, we have another function to do just that!
CumulativeSum([Order Count])This Order Count Running Sum is the numerator we need to calculate our running % of total for our y axis. Let’s create our last calculation dividing the daily running order count into the yearly total order count and format it as a percent
[Order Count Running Sum] / [Order Count_year]Your groupings and child table should look something like this
Let's build the chart
Now that we have everything we need to create our final line chart, we need to create a child chart based on our child table Yes, that means our child table is now parent of the chart. This is one of the fundamental concepts that you may need to spend some time to fully grasp. It is a mental shift from other tool where you only get 1 table and all your calculations live in one massive table. Sigma, brilliantly, splits these things up into manageable pieces keep your logic as clean as possible.
So let’s click on the “+ element” button in the upper right corner of our table and let’s add a chart
You can format your Year colors however you wish, I formatted 2025 to be highlighted among the others. So the line chart is created, now let’s make it interactive and actionable. To do that I’ll add a slide control to allow users to select a percentage between 0-100 and I’ll default to 50.
One thing to keep in mind with controls is that they “live” outside the data source or table. You either have to write a function or tie a control to a column in order to make interactive with your data. So let’s do that now.
Let’s go back to our child table and add a grouped calculation at the year level and we’ll simply reference our slide control and divide it by 100 to convert it a percentage. This is probably a good time remind you about best practices related to controls. I always rename my controls with the prefix “ctrl” so that when I’m referencing them in calculations, I can quickly find them. You should do that as well.
Now that we have our Target control in our table we can use this so many different ways. The first way we’re going to use is to add a reference line in our chart. By navigating to the Format tab of our line chart, you can see we have a lot of options lets expand “Reference Line” and click on “Add New” and set it up like so
We are so close now. Let’s add some Product page controls to allow for users to discover their insights. To do so, we’ll add these from our Parent transaction table that we first connected. Each subsequent element we created is a child of this table so any change we make to it will flow to all child elements.
These page controls can be created in a couple ways. I personally start by creating a basic table filter and then converting to page control. But if you feel like it, you can do it from the control interface. Its just a few extra clicks to set it up. Once you set the controls we can start put this app together.
So before we wrap a bow on this I went back to the title of this challenge “When did we reach 50% of…”
However, we never really answer that question with this chart, do we? Let’s add a simple table so show, by year, the date and day of the year we achieved the target percentage.
Let’s go back to our Year child table and we’ll add a grouped calculation that references our daily % and the dynamic target
Min(If([Running Pct of Total order Count] >= [Target], [Day of Order Date (Sales)]))
This calculation is evaluating our daily running % calc, and returning the first (minimum) date where that percentage is greater than or equal to our target value.
Let’s create an additional child element but this time let’s do a table. And then Sigma allows you select the level of grouping you want to show so we only want to select to the Year of Order Date grouping and boom!
DISCLAIMER: This is a Sigma sponsored post
Go Forth and Viz