3 Replies Latest reply on Apr 12, 2016 9:57 AM by Dale Wunderlich

    Compare averages before or after a change - Table Calc

    Dale Wunderlich

      I'd like to know what trades have been beneficial and what have been deleterious. I have a metric, in this simplified sample it's points per game and the date when a trade occurred.

      Can anyone think of a clever way to untangle such a mess? In my sample I'd like to show that Bill's move to the Raptors was great, but Steve's move to the Lakers was not, eventually I'd like to show what teams make to most out of trades.  My actual set has tens of thousands of rows, so a bit more complicated.

      Thanks, any ideas or thoughts about what kind of analysis this is so I can better search for a technique would be appreciated!

       

      dw

        

      NameYearTeampointsPerGame
      Bill2001Kings23
      Bill2002Kings13
      Bill2003Kings9
      Bill2004Raptors34
      Bill2005Raptors32
      Bill2006Raptors30
      Steve2001Nuggets32
      Steve2002Nuggets34
      Steve2003Lakers9
      Steve2004Lakers8
      Steve2005Lakers8
      Jake2004Bulls3
      Jake2005Suns

      8

       

        

      NameMoveYearOldTeamNewTeam
      Bill2004KingsRaptors
      Steve2003NuggetsLakers
      Jake2005BullsSuns
        • 1. Re: Compare averages before or after a change - Table Calc

          Hey Dale,

           

          The most blatant issue is that the Celtics somehow aren't on this list.

           

          I've actually asked for a bit of help on this one so hopefully we can get you a good answer!

           

          -Diego

          • 2. Re: Compare averages before or after a change - Table Calc
            Patrick A Van Der Hyde

            Hello Dale,

             

            I did this in 9.3 so I am hoping you have downloaded and installed this new version.  If not, you can install 9.3 alongside an older version and have them both run on the same license.

             

            here is the outcome view of the example I created:

             

             

            I did this by creating a field that could be referenced for the avg across all years per player:  {Fixed [Team],[Name]: Avg([Points Per Game])}

             

            Then i created data rows that only include the first year a player is on a team - {fixed [Team],[Name]: Min([Date])}

             

            This allows for the creation of the view above without needing all of the other years while still showing the average across years.  Year(First Date) is in front of TEam name on Rows to force the Sort by first date on a team.

             

            Then I do the comparison between teams per player:

             

            If Index()>1 then

            if Avg([Avg Points LOD])>lookup(Avg([Avg Points LOD]),-1) then "Green" else "Red" END

            else "First Team" END

             

             

            The Index() function is set to restart for each Name so we are only comparing rows for the same player.  To do this, I needed to use the Table Calculation Advanced Compute options:

             

            Then the rest was just setting the colors and shapes for the outcome of the calculation - Green, Red, or First Team.

             

            You can also remove the first Avg column from the Measure names if you like.  I just had it there to compare results and make sure all was right. 

             

            Patrick

            1 of 1 people found this helpful
            • 3. Re: Compare averages before or after a change - Table Calc
              Dale Wunderlich

              Ah Fixed! that should help I really need to study up on that one.

               

              This might be a step too far but is there any way to aggregate on the team, let me know if I'm crazy.

               

              Thanks,

              dw