4 Replies Latest reply on Aug 12, 2016 1:22 AM by swaroop.gantela

    Fix Control Chart Limits for Last Stage

    Michael Smith

      Hi,

       

      I'm a new user and I'm struggling with how to fix the limits of the last control chart stage to the limits from the previous stage.  I've attached a packaged workbook as an example.

       

      The UCL calculation is contained in the calculated field 'ProdLT2_UCL' with the following calculation:

       

           WINDOW_AVG(sum([ProdLT2]))+(3*WINDOW_AVG([ProdLT2_mR])/1.128) (Grouped Across Pane)

       

      The Panes are determined by a 'StageDeliveredID' dimension that is calculated as follows:

       

           if [DateDeliveredID] <= "201506250134" then 1

           elseif [DateDeliveredID] <= "201603140135" then 2

           else 99 end

       

      The dimension value of 99 indicates that this set of data is to evaluated using the limits from the most recent stage (in this case stage 2).

       

      To accomplish this I was hoping to modify the UCL calculation as follows, but I've not been able to accomplish this in a manner that creates a valid calculation.  Here is what I've been attempting to do.

       

      IF ATTR(StageDeliveredID) <> 99

      THEN

           WINDOW_AVG(sum([ProdLT2]))+(3*WINDOW_AVG([ProdLT2_mR])/1.128) (Grouped Across Pane)

      ELSE

           PREVIOUS_VALUE or LOOKUP( WINDOW_AVG(sum([ProdLT2]))+(3*WINDOW_AVG([ProdLT2_mR])/1.128),-1)

      END

       

      I would appreciate any help.  It is the last item that I need to accomplish to move my dashboards from Excel to Tableau.

        • 1. Re: Fix Control Chart Limits for Last Stage
          Michael Smith

          I've gotten this much further:

           

          IF ATTR([StageDeliveredID]) <> 99 THEN

              (avg([ProdLT2]))+(3*WINDOW_AVG([ProdLT2_mR])/1.128)

          ELSE

              0

          END

           

          This correctly set last stage (99) UCL to 0.   The question now becomes how to reference the UCL from Stage 2.  I've tried the following as the else calculation

           

               PREVIOUS_VALUE((avg([ProdLT2]))+(3*WINDOW_AVG([ProdLT2_mR])/1.128)) which gives      me 110.4 instead of the 220.2

           

               LOOKUP((avg([ProdLT2]))+(3*WINDOW_AVG([ProdLT2_mR])/1.128)) which gave me the      original value of 138.8

          • 2. Re: Fix Control Chart Limits for Last Stage
            swaroop.gantela

            Michael,

             

            Please see if the attached could be a first step.

            I think the issue is that you need a different

            "Compute using" to ask for the UCL of the previous StageDelivered.


            And so I split the UCL into two parts:

            For non-last stage with "Compute using" of "Pane (Across)":

            IF ATTR([StageDeliveredID]) <> 99 THEN

                [ProdLT2_UCL]

            END

             

            and for last stage with Advanced "Compute using" shown below:

            IF ATTR([StageDeliveredID]) = 99 THEN

               LOOKUP([ProdLT2_UCL],-1)

            END

             

            212815ucl.png

            1 of 1 people found this helpful
            • 3. Re: Fix Control Chart Limits for Last Stage
              Michael Smith

              Sawroop,

               

              Thanks for your response.  I was able to take your suggests, build on them, and get to what I was looking for.

               

              The basic upper limit (UCL) is 'ProdT2_UCL'  calculated pane (across) as follows:

                  WINDOW_AVG(sum([ProdLT2]))+(3*WINDOW_AVG([ProdLT2_mR])/1.128)

               

              This is then used to calculate 'ProdLT2_CL_Chart as follows:

                  IF ATTR([StageDeliveredID]) <> 99

                  THEN [ProdLT2_CL]

                  ELSE LOOKUP([ProdLT2_CL],-1)

                  END

               

               

              I applied a similar technique to the create the centerline (replacing what was previously an average reference line).

               

              I also created a 'ProdLT2_UCL_Max' calculation to use in LCL - UCL reference line to control the shading on the chart so that the reference stages (<> 99) are shaded while the current stage is not shaded.

               

              Here is a current version of the workbook for anyone who would like to see this.  Just be aware that I have not yet applied the concepts to the moving range  part of the chart.

              • 4. Re: Fix Control Chart Limits for Last Stage
                swaroop.gantela

                Michael,

                 

                Nicely done, sir.

                Thanks for posting.

                All the best.