Help with some Set Analysis

[danielact]

I have data for employees going back a few years. I'm trying to show some historical data, which is simple enough. I want to show current salary, as well as salary info going back a few years. I can easily use set analysis to do this. The issue is when I have another dimension which may change over time. Here's an example:

EmployeeName     Position     Year     Salary

John Smith          Low-Level     2012     50,000

John Smith          Mid-Level     2013     65,000

John Smith          High-Level     2014     85,000

I want to have a pivot table which would look as below. The user would select what the current year they want to see is, and the others would update to automatically show the most recent 3 years of history, but using the current level details:

Position          Name          2014 Salary     2013 Salary     2012 Salary

High-Level        John Smith     85,000          65,000          50,000

I use set analysis to set the year I want for the salary history in the expression. However, because in different years the employee was at different positions, I'm getting this:

Position          Name          2014 Salary     2013 Salary     2012 Salary

High-Level       John Smith     85,000          0                    0

Mid-Level        John Smith     0                    65,000          0

Low-Level        John Smith     0                  0                    50,000

Any ideas on how to get to the table I want?

[maxgro]

if you want one row for employee

John Smith     85,000          65,000          50,000          High-Level

position should be an expression calculated as 2014 salary, I suppose

only({$ <Year={$(=max(Year))}>} Position)