Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 1st 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!
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
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
Many thanks Stefan! Now I know one more useful function! Very easy actually.
Marko