1 Reply Latest reply on Dec 12, 2019 3:34 PM by Zhouyi Zhang

    Standard Deviation - Help

    Juan Contreras

      I have the following table i've created in Excel and am trying to re-create in tableau exempt I don't want to display the years in tableau.  My Overall goal is to create a control chart that adjusts month where the Range of the control chart is between the Upper Control Limit (UCL) and Lower Control Limit (LCL).

       

      Calculations are:

       

      Weighted Value = (Values * Weight)

      Sum Weighted Value = Sum(Weighted Value)

      Standard Deviation = stdev.p(Values)

      UCL = Sum Weighted Value + Standard Deviation

      LCL = Sum Weighted Value - Standard Deviation

       

      Raw Data

      YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
      2015170181754131852644462963370002367
      20166641625167217445535553274563985203072879108
      20172114154169015213572185305244133763535235
      20182374694586224233713603594554343262664780
      201928121754244642246123650242345638604372
      TOTALS15632907316341262104200815872143213718231395906

      25862

       

      Monthly Weighted Totals (Need to wait 2015 @ 5%, 2016 @ 10%, 2017 @ 20%, 2018 @ 25%, 2019 @ 40%)

             

      YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
      201599421913221517000118
      2016661631671745556334640523129911
      201742838318010471441061058375711047
      20185911711515610693909011410982671195
      2019112872171781691849420116918215401749
      TOTALS2894595857094434172834574444263421665020

       

      Monthly Standard Deviation

              

      YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
      Standard Deviation345073615457306355615231

       

             

      JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
      UCL323509658770497474313520499486394197
      LCL255408513648390360253393390365289135

       

      End Goal is to create a chart like the following where the gray shaded area is between the UCL and LCL.