Control charts are a great way to analyze variation over time. They use a statistical process to define limits that should be observed to identify unusual behaviors that require additional investigation.
Control charts are commonly used in manufacturing to ensure product quality over time, in healthcare to monitor patient progress, and in finance to monitor important KPI (key performance indicator) moves and their consequences.
In this blog, we’ll look at how you can use your Omni platform’s native tools to create this type of chart.
What is Omni?
Omni is a business intelligence platform with an intuitive user interface for self-service analytics. Users work with a spreadsheet-like environment that enables them to manipulate and analyze data quickly.Â
The platform combines the consistency of a shared data model with the flexibility of SQL and the familiarity of Excel-like formulas to create new calculations, allowing users to explore data questions while developing a reusable and managed data model as they go.
Omni combines the ability to develop fast time-to-value and democratize access with the governance required to curate metrics in a centralized management system and define clear permissions for any access level.
The platform also supports bidirectional dbt integration, which simplifies data modeling. It automatically syncs dbt schema refreshes and modifications, ensuring that transformation and model updates are reflected in Omni immediately and eliminating unnecessary work between dbt and the BI layer.
Finally, you’ll find AI-powered features such as natural language querying for data answers, an AI formula generator for adding results to tables, an AI query helper (Blobby) for query refinement, and AI summary, which adds an explanation of your data to dashboards based on whether you provide context.
Building a Control Chart
Control charts can be created in Omni using only its native tools, such as Excel-like formulas for statistical metrics and a selection of line charts. This will look like the following:
Let’s explore how you can tailor that to your own use case.
Preparing The Workbook
The first step in creating your control chart is organizing your data in the workbook so that you can define the X and Y axes. In this scenario, we will develop a control chart to track total sales over time.
We will create a simple two-column table with the month and total sales, as shown in the screenshot below.
That will serve as the foundation for developing the statistical indicators necessary for the control chart. That varies depending on the control chart’s goals, but we’ll start with the most frequent case: using the average and standard deviation to calculate the control limits.
Calculating The Limits
For this section, we’ll begin by calculating the average line on top of the base table you accomplished before. That is a straightforward task that you will do using Omni’s Excel-like formulas.
 To begin, create a new calculation column in your workbook by hovering over the measure you will use, in this case Total Sales, to locate the three dots button. To build it, use the Insert Column Right button, or click the icon displayed below in the upper right corner of Omni.
Fill the new column with the average formula, which should look like this:
=AVERAGE(B:B)
The B:B is the field containing the numbers from which you want to calculate your average. Change it as necessary to fit in your table. By clicking twice on the column name, you can rename it as desired. In this case, we renamed it Average.
 The next step is to calculate the control limits using the average and standard deviation. The logic will be similar between them, with slight alterations, but, as with the average column, we will generate a new column for each control limit.
 For this example, we will start by establishing the upper limits using the formulas listed below.
Calculation Name | Formula |
|---|---|
1st Upper std deviation |
|
2nd Upper std deviation |
|
Upper Control Line |
|
The STDEV() formula calculates the standard deviation of the measure field being used for the control. To generate the upper limits, add the standard deviation and associated multipliers to the average line. This ensures that your chart limitations are always current based on the data available at the time, rather than static limits that may no longer fit in future analyses.
The lower limits are built in the same way as the higher limits, except the average and its multipliers are subtracted from the average. The table below shows that.
Calculation Name | Formula |
|---|---|
1st Lower std deviation |
|
2nd Lower std deviation | =C:C - (STDEV(B:B) * 2) |
Lower Control Line | =C:C - (STDEV(B:B) * 3) |
Remember that we created the average field first, so we used it in the limit columns instead of recalculating the average for every limit formula, which simplified the calculation process.
You will end up with a table like the one below.
You are now ready to create your control chart.
Building The Chart
We can now configure the control chart in your results table in the same way that we would use a traditional multi-line chart.
You can select the Lines type of chart from the Chart selector on the right side. If you do not see the menu, please click the Options button at the top.
Now, scroll down the right menu of Chart Options to the Available Fields section and drag the dimension you want to utilize, in this example Month, to the X axis. After that, move each measure to the Y axis. The fields can be organized as you choose, but in our example, the top limits come first, followed by the lower limits.Â
You will have already created your control chart, but for best practices on chart readability, we will go a little further in this blog and make a few modifications to make it easier to read.
The next steps will configure each line in the chart. Use a high-contrast color and dots to highlight the primary line you’re controlling. Your needs will determine the line interpolation, but linear or monotone is the preferred option.
You can set up the average, first upper, and first lower lines to be a small dashed line so that it does not draw too much attention away from the main line.Â
Use the dashed line rather than the small dashed line to emphasize the second upper and lower lines in case the main line crosses them. Finally, the upper and lower control lines might follow the usual line configuration. Their solidity will create an acceptable perception of ceiling and floor limits for this chart.
Colors can be used to make your dashboard more visually appealing and easier to understand. Just make sure not to add too much, as this can pollute the visual appeal.
Highlighting Outliers
As the last step in making your control chart easier to understand, we should implement a method to highlight any points that fall outside the bounds. Highlighting that information allows users to easily recognize exceptional incidents that require additional attention.
You can highlight these outliers with a different color by establishing a new calculated field with Excel-like calculations. That new field will be utilized to generate a new scatter plot, only with outliers to overlap the line chart.
To produce this scatter plot with outliers, add a new column named “Outliers”. That column should be added at the end and will contain a formula that only returns outliers.
For that example, it should look like this:
=IF(OR(B1 > F1, B1 < I1), B1, NULL)
In the Chart view, you will add this new field to the same axis as the other lines and set the chart mark to scatter.
You can also choose a particular color for creating contrast on these outlier points. In this case, we used red as the color.
In this case, there is no data coming out of the upper or lower bounds. To demonstrate how that highlight will appear, we will set the 2nd upper limit as the threshold for highlighting outliers. It will appear like this:
This also opens up the possibility of assigning various highlights to points in different areas of control. You can, for example, color it yellow between 2nd Upper std deviation and Upper control Line and red for anything beyond the Upper control Line.
Conclusion
As demonstrated in this blog, you can make control charts in Omni using just native features by properly structuring your data and designing a multi-line chart so that the calculated limits can be used as reference lines.
Omni has an excellent balance of native features for creating various types of charts and the ability to go further by integrating Vega-Lite and HTML components for additional customization. This allows you to create beautiful and insightful dashboards to drive business decisions.
Want to do more with your data?
At phData, we help organizations get the most out of tools like Omni by combining deep data expertise with hands-on experience building powerful, scalable dashboards. Whether you’re just getting started or looking to level up your analytics, our team can help.
