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

    Count of an aggregate function (or if condition)

    daniel melo

      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