4 Replies Latest reply on Sep 23, 2016 12:41 PM by sandra cuentas

# Variable Control Limits for X Chart

Dear Community,

I found this workbook online and i'm wondering if there is a way to plot a conditional average line for specific time periods. The result would be (for example) a single average line that shows an average from 2009 to 2010, and is later segmented to show another average  including values from 2011 to 2013. Control limits should change accordingly.

• ###### 1. Re: Variable Control Limits for X Chart

Hi Sandra,

Sure there's a way! You can use 2 parameters ([Start Date] and [End Date]) to indicate the beginning and end of your timeframe. With those, you can create a calculated field like this one:

[Sales within timeframe] : IF [Order Date] >= [Start Date] AND [Order Date] <= [End Date] THEN [Sales] END

[Std Lower Bound] = WINDOW_AVG(SUM([Sales within timeframe]))-WINDOW_STDEV(SUM([Sales within timeframe]))*[Standard Deviations]

[Std Upper Bound] = WINDOW_AVG(SUM([Sales within timeframe]))+WINDOW_STDEV(SUM([Sales within timeframe]))*[Standard Deviations]

For you to be able to use the calculated field on the reference line, you can drag it to 'Detail' and then edit the reference line:

Attached working copy in version 10.0.

• ###### 2. Re: Variable Control Limits for X Chart

Hi Santiago,

Thank you very much for your effort and help!. Is it possible to use your solution to view both settings within a single chart? For example if i choose two time periods one from 2009 to 2011 and another from 2011 to 2013, is it possible to have different average and limits for both and still show that in a single graph?

The final result would be something like this.

Thank you again.

• ###### 3. Re: Variable Control Limits for X Chart

You can use a similar approach (create different calculated fields) to add more reference lines, an example is attached. Keep in mind that as you add more elements to any viz, there's more for the end user's brain to process. You could use the technique described here to select wich reference lines are shown (the general or timeframe average): Swapping Measures Using Parameters | Tableau Software

Simple is -almost always- better.

• ###### 4. Re: Variable Control Limits for X Chart

Hi Santiago,

Sorry for the late reply...You are right, by adding more reference lines, there are too many elements in the viz. Thank you very much for your time and help .