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

Compare averages before or after a change - Table Calc

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

 Name Year Team pointsPerGame Bill 2001 Kings 23 Bill 2002 Kings 13 Bill 2003 Kings 9 Bill 2004 Raptors 34 Bill 2005 Raptors 32 Bill 2006 Raptors 30 Steve 2001 Nuggets 32 Steve 2002 Nuggets 34 Steve 2003 Lakers 9 Steve 2004 Lakers 8 Steve 2005 Lakers 8 Jake 2004 Bulls 3 Jake 2005 Suns 8

 Name MoveYear OldTeam NewTeam Bill 2004 Kings Raptors Steve 2003 Nuggets Lakers Jake 2005 Bulls Suns
• 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

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

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