help needed -- set analysis question

[#1]

Hi all,

Please consider the following example:

SalesPerson	Sales	Year
B	13	2010
C	14	2010
A	22	2011
B	23	2011
C	24	2011
A	32	2012
B	33	2012
C	34	2012
D	35	2012
A	42	2013
B	43	2013
C	44	2013
D	45	2013

SalesPerson	Joined
A	2011
B	2010
C	2010
D	2012

I want to get their sales figures in the 1^st year of their sales job. Therefore, results should be as the following:

Sales Person	Sales Year 1	Sales Year 2	Sales Year 3	Sales Year 4
A	22	32	42	-
B	13	23	33	43
C	14	24	34	44
D	35	45	-	-

What is the best way to get this working? I tried set analysis within sum(), aggr().. no luck

Thanks!

[swuehl · Accepted solution]

I assume you won't have records for your SalesPerson prior their joining dates.

Then it should be enough to create a table with dimension SalesPerson and four expressions

=FirstSortedValue(Sales, Year,1)

=FirstSortedValue(Sales, Year,2)

=FirstSortedValue(Sales, Year,3)

=FirstSortedValue(Sales, Year,4)

See also attached sample.

Hope this helps,

Stefan

[#3]

Many thanks Stefan! Now I know one more useful function! Very easy actually.

Marko