Help with some Set Analysis

danielact — Wed, 10 Sep 2014 17:06:00 GMT

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?

Re: Help with some Set Analysis

maxgro — Wed, 10 Sep 2014 21:13:30 GMT

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)

topic Help with some Set Analysis in QlikView

Help with some Set Analysis

Re: Help with some Set Analysis