Hi all,
Lets say I have the following table
| Person ID | Pers.No. | Start Date | End Date | Status |
| 1 | 43987 | 5/1/2014 | 12/31/9999 | 0 |
| 1 | 43987 | 4/1/2014 | 4/29/2014 | 3 |
| 2 | 2 | 8/1/2005 | 12/31/9999 | 3 |
| 3 | 25178 | 4/1/2009 | 12/31/9999 | 0 |
| 3 | 25179 | 10/17/2014 | 12/31/9999 | 3 |
I want to count the number of persons with status equal to 3 which start date is the greater for that person. In this case the count would be 2 for status 3(Person ID 2 and 3) and 1 for status 0 (Person ID 1). Is there a way to do it in set analysis?
I am thinking in create a new column as follows:
| Person ID | Pers.No. | Start Date | End Date | Status | Column Status 3 | Column Status 0 |
| 1 | 43987 | 5/1/2014 | 12/31/9999 | 0 | 0 | 1 |
| 1 | 43987 | 4/1/2014 | 4/29/2014 | 3 | 0 | 0 |
| 2 | 2 | 8/1/2005 | 12/31/9999 | 3 | 1 | 0 |
| 3 | 25178 | 4/1/2009 | 12/31/9999 | 0 | 0 | 0 |
| 3 | 25179 | 10/17/2014 | 12/31/9999 | 3 | 1 | 0 |
In this way I just sum the values of the new columns.
The idea would be, for every PersonID, select the max Start date, and check whether it has 0 or 3 in the Status column:
=Aggr(Max([Start Date]),[Person ID])
Any Idea?
Thanks.
Message was edited by: daniel melo
