2 Replies Latest reply: Nov 28, 2017 6:25 AM by daniel melo

# Count of an aggregate function (or if condition)

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

• ###### Re: Count of an aggregate function (or if condition)

Hi,

you said: 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)

but here the greater startdate value is 10/17/2014 which is the PersonID=3 only, so count => 1

I'm I wrong ?

• ###### Re: Count of an aggregate function (or if condition)

Yes, you are right. I'm sorry. Edited now!