Creating a Dynamic Control Chart in Tableau

Control Charts are typically used to monitor business processes over time, or variations in products or product categories. A control chart always has a central line for the average, an upper line for the upper control limit and a lower line for the lower control limit. The simplest and fastest way to build a control chart in tableau is adding a distribution band from the analytics pane, and setting the upper and lower limits to -m.+m standard deviations from the average.

In this blog I demonstrate how to make a control chart dynamic in 6 steps. i.e : parameterizing the upper and lower limits of the chart and coloring product categories accordingly.

Control Chart of Sales and Profit by Product Subcategory ( Sample Superstore Data)

Step : 1

Find the Average Profit and Average Sales:

1. WINDOW_AVG(sum([Profit]))
2. WINDOW_AVG(sum([Sales]))

Step : 2

Find the Standard Deviation from the Averages:

1. WINDOW_STDEVP(SUM([Profit]))
2. WINDOW_STDEVP(SUM([Sales]))

Step : 3

Create a Number of Standard Deviations Parameter

Step : 4 

Find the Positive/Negative difference from the average

1. [Fixed Average Sales]+ [Number of Stdev]*[Stdevp_Sales]
2. [Fixed Average Sales]- [Number of Stdev]*[Stdevp_Sales]
3. [Fixed Average Profit]+ [Number of Stdev]*[Stdevp_Profit]
4. [Fixed Average Profit]- [Number of Stdev]*[Stdevp_Profit]

Step: 5

Add Reference Lines using the Calculations above after adding those calculations to detail.

Step: 6

Create Color Calculations

1. Color1=  if sum([Sales])>= [Positive Difference_Sales]
   or sum([Sales])<= [Negative Difference_Sales]
   then True else False
   end
2. Color2= if sum([Profit])>= [Positive Difference_Profit]
   or sum([Profit])<= [Negative Difference_Profit]
   then True else False
   end

and Finally add both calculations to color, and show your parameter control.