Count of an aggregate function (or if condition)

danimelo1 — Tue, 28 Nov 2017 11:11:14 GMT

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)

YoussefBelloum — Tue, 28 Nov 2017 11:20:30 GMT

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)

danimelo1 — Tue, 28 Nov 2017 11:25:54 GMT

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

topic Count of an aggregate function (or if condition) in App Development

Count of an aggregate function (or if condition)

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

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