Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts:
Based on this table I neet to count the total number of changes on Positions.
Person ID | Pers.No. | Start Date | End Date | Act. | ActR | S | Position | Key |
00003928 | 3928 | 6/1/2012 | 12/31/9999 | U4 | U3 | 3 | 10001601 | KP |
00004211 | 4211 | 10/11/2012 | 12/31/9999 | U4 | U3 | 3 | 10001717 | KP |
00039396 | 39396 | 12/1/2011 | 12/31/9999 | U4 | U6 | 3 | 10001682 | KP |
00025391 | 52520 | 8/1/2014 | 12/31/9999 | 08 | 3 | 62000483 | KP | |
00025391 | 52520 | 8/1/2014 | 12/31/9999 | U4 | U3 | 3 | 62000483 | KP |
00054245 | 54245 | 9/1/2014 | 12/31/9999 | U0 | U7 | 3 | 10001709 | KP |
00054245 | 54245 | 9/1/2014 | 12/31/9999 | U4 | U6 | 3 | 10001709 | KP |
00029806 | 43279 | 11/1/2013 | 5/17/2015 | U3 | U1 | 3 | 70016769 | KP |
00029806 | 43279 | 5/18/2015 | 6/30/2015 | U3 | U1 | 3 | 70016769 | KP |
00029806 | 43279 | 7/1/2015 | 9/30/2015 | U3 | U1 | 3 | 70016769 | KP |
00012541 | 58574 | 11/1/2015 | 12/31/9999 | 08 | 3 | 70016769 | KP | |
00026447 | 26447 | 7/1/2014 | 12/31/9999 | U3 | U1 | 3 | 70082489 | KP |
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
Or this
=Count(DISTINCT Aggr(If([Person ID] <> FirstSortedValue(DISTINCT TOTAL<[Position]> [Person ID], -[Start Date]), Position), [Person ID], Position, [Start Date]))
May be you need this?
=Count(Aggr(If([Person ID] <> FirstSortedValue(TOTAL<[Position]> [Person ID], -[Start Date]), Position), [Person ID], Position, [Start Date]))
Or this
=Count(DISTINCT Aggr(If([Person ID] <> FirstSortedValue(DISTINCT TOTAL<[Position]> [Person ID], -[Start Date]), Position), [Person ID], Position, [Start Date]))
Thanks so much Sunny. Just what I was looking for!