3 Replies Latest reply: Dec 6, 2017 11:21 AM by daniel melo RSS

    Count based on aggregate date

    daniel melo

      Hi experts:

       

      Based on this table I neet to count the total number of changes on Positions.

       

            

      Person IDPers.No.Start DateEnd DateAct.ActRSPositionKey
      0000392839286/1/201212/31/9999U4U3310001601KP
      00004211421110/11/201212/31/9999U4U3310001717KP
      000393963939612/1/201112/31/9999U4U6310001682KP
      00025391525208/1/201412/31/999908 362000483KP
      00025391525208/1/201412/31/9999U4U3362000483KP
      00054245542459/1/201412/31/9999U0U7310001709KP
      00054245542459/1/201412/31/9999U4U6310001709KP
      000298064327911/1/20135/17/2015U3U1370016769KP
      00029806432795/18/20156/30/2015U3U1370016769KP
      00029806432797/1/20159/30/2015U3U1370016769KP
      000125415857411/1/201512/31/999908370016769KP
      00026447264477/1/201412/31/9999U3U1370082489KP

       

       

      At first I was trying to group data based on Position, and the count the Person ID, but didn't worked: aggr(count (Person ID), Person ID, Position).

      The expected output should be 1 (regarding to this 'Position'70016769, that has changed from Person ID 29806 to 12541)

       

      Then I searched and found this, is quite similar to what I need, but I can't make it work:

      =count(aggr(If(Position= FirstSortedValue(TOTAL<[Person ID]> Position, -[Start Date]), Position),[Person ID],Position,[Start Date]))

       

      The result is 8, and it seems is counting changes based on Start Date.

       

      Any idea?

       

      Thanks