Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

danimelo1
New Contributor III

Count of an aggregate function (or if condition)

Hi all,

Lets say I have the following table

   

Person IDPers.No.Start DateEnd DateStatus
1439875/1/201412/31/99990
1439874/1/20144/29/20143
228/1/200512/31/99993
3251784/1/200912/31/99990
32517910/17/201412/31/99993

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 IDPers.No.Start DateEnd DateStatusColumn Status 3Column Status 0
1439875/1/201412/31/9999001
1439874/1/20144/29/2014300
228/1/200512/31/9999310
3251784/1/200912/31/9999000
32517910/17/201412/31/9999310

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

2 Replies
YoussefBelloum
Esteemed Contributor

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 ?

danimelo1
New Contributor III

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

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