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
  2. Color2= if sum([Profit])>= [Positive Difference_Profit]
    or sum([Profit])<= [Negative Difference_Profit]
    then True else False

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