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

    Variable Control Limits for X Chart

    sandra cuentas

      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.

       

      Thanks in advance.

        • 1. Re: Variable Control Limits for X Chart
          Santiago Sanchez

          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

           

          Now your calculated field only represents sales within your selection. You can use this one instead of [Sales] in your reference line and in your upper/lower bounds:

           

          [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
            sandra cuentas

            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
              Santiago Sanchez

              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
                sandra cuentas

                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 .