4 Replies Latest reply on Jul 28, 2015 1:29 PM by matthew.eisenberg

    Calculating New Field W/ Filtered Data

    matthew.eisenberg

      Hello,

       

      Everyone here has been a tremendous help with each question I have previously had, I figured why not come back here with my next question. 

       

      The following is what I am currently working on: https://public.tableau.com/views/MatchupComparison/Sheet4?:embed=y&:display_count=yes&:showTabs=y

      e26iekE.png

      The Team filter is currently set to show two teams that are selected by a parameter using the condition [Team]=[Team Select #1:]or[Team]=[Team Select #2"]. 

       

      Team Pythag is a calculated field on its own that works with Offensive and Defensive Efficiency across a single row. 

       

      What I am trying to do next is a create a calculated field for "Expected Win Percentage".  This would take bits of data from both Row 1 and Row 2.  Looking around, it would appear that "lookup" and "first" is the best way to do this, correct?

       

      Expected Win % = (Team Pythag A - Team Pythag A * Team Pythag B) / (Team Pythag A + Team Pythag B - 2*Team Pythag A*Team Pythag B).

       

      Any assistance or advice would be greatly appreciated.

       

      Thanks,

      Matt

        • 1. Re: Calculating New Field W/ Filtered Data
          swaroop.gantela

          Matthew,

           

          You can try:

           

          (LOOKUP(ATTR([Team Pythag]),0) - (LOOKUP(ATTR([Team Pythag]),0)*LOOKUP(ATTR([Team Pythag]),1))) /

          ( (LOOKUP(ATTR([Team Pythag]),0)+

             LOOKUP(ATTR([Team Pythag]),1)-

             (2*LOOKUP(ATTR([Team Pythag]),0)*LOOKUP(ATTR([Team Pythag]),1))))

           

          With a "Compute using" of 'Team'

          • 2. Re: Calculating New Field W/ Filtered Data
            matthew.eisenberg

            Thank you for this formula.

             

            It produces an outcome for one of the two teams.  Would there be then a way to produce a figure for the second team as well?

             

            This concept of looking up attributes is definitely new to me and I apologize for what might be simple tasks I am overlooking for the time being. 

             

            Further down the line, there are other calculations where I hope to take part of row 1 and part of row 2 as well.  While the calculation of win percentage above has a total of 100%, the future calculations I hope to use will not have any base total or sum.  For example, I hope to create a "predicted offensive efficiency" total for each team.  This will include multiplying team A offensive efficiency by team B defensive efficiency by a national average (TBD figure). 

            • 3. Re: Calculating New Field W/ Filtered Data
              swaroop.gantela

              Matthew,

               

              No problem about the ATTR, was only using that because the LOOKUP required some kind of aggregation.

               

              Is the calculation for Team B equal to 1-Team A?

              If not, you can use the same formula and just change all the

              LOOKUP(ATTR([Team Pythag]),1) to

              LOOKUP(ATTR([Team Pythag]),-1)

              which will lookup the value in the top line.


              Then you can make one Calculated field

              IF INDEX()==1 THEN Expected Win % A

              ELSE Expected Win % B

              END


              So with this approach, you would need to make two calculated fields for every metric,

              one for Team A and one for Team B, and then one more piece to say if Team A then

              show the metric for Team A, etc.


              There are likely more efficient ways to do this.



              1 of 1 people found this helpful
              • 4. Re: Calculating New Field W/ Filtered Data
                matthew.eisenberg

                That helps, thank you.

                 

                Yes, in the case of win% team B would be =1-Team A