3 Replies Latest reply on Aug 17, 2016 11:45 AM by Carl Slifer

    recursive sum using 2 columns

    vinutha vasanthu

      I have a requirement where i need to calculate the comm.Quality score.Please find the xls and my tableau

      work book attached to this.

      Yield is considered as Quality score(X) and i need to calculate the Comm Quality score(Y)

      Formula for Comm Quality Score(Y): Y1=X1

                                         Y2=(Y1+X2)/2

                                         Y3=(Y2+X3)/2....

      Please help me with your valuable suggestions.

        • 1. Re: recursive sum using 2 columns
          Carl Slifer

          Howdy Vinutha,

           

          In order to do this we use a table calculation. A table calculation allows you to use the values that you see in the view which are already aggregated.  We also need to make the first cell of the partition act differently. Because in the syntax it would be looking up a value that could not exist (a value before it).

           

          IF FIRST() = 0 THEN SUM([Yield])

          ELSE (LOOKUP(SUM([Yield]),-1) + SUM([Yield]))  / 2

          END

           

          So the function above says if its the first cell in a partition (row in this case) then return the sum of yield if it is not, lets say it is the third it will return the sum of the second and third month divided by two.  I then made sure that the table calculation was running 'table across'. I assume you wanted each program calculated separately.@

           

          Carl Slifer

          InterWorks

          3 of 3 people found this helpful
          • 2. Re: recursive sum using 2 columns
            vinutha vasanthu

            Thanks Carl,

            The Recursion computed is average value of previous yield and current yield but as per my requirement it is average value of current yield and previous month calculated recursion.

            Please find the screen shot attached.

             

            • 3. Re: recursive sum using 2 columns
              Carl Slifer

              Hi Vinutha,

               

              This sounds more like a rounding problem. Your values have more decimals than you are acknowledging. in your calculation.

              For example Yield has 9+ significant figures. Also 99.74 and 99.77 would if they were just those two values with no trailing decimals would round to 99.755 or 99.76 when you round properly. So the comparison is not on target either. In order to make this value what you expect it to be you will need to use the ROUND() function around each portion of the calculation (around the SUM([Yield) and around the lookup of the sum yield)  so that the numbers fit your expected.

               

               

               

              However using the calculations show above if my values do not fit yours you should be able to use table calculations namely the lookup function to solve your goals. My goal here is to teach people to fish instead of giving them fish so I would hope instead of solving every issue for each person to be able to give them the tools and know how to solve future issues on their own.

              1 of 1 people found this helpful