Count based on aggregate date

danimelo1 — Wed, 06 Dec 2017 15:58:32 GMT

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

Re: Count based on aggregate date

sunny_talwar — Wed, 06 Dec 2017 16:09:10 GMT

May be you need this?

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

Re: Count based on aggregate date

sunny_talwar — Wed, 06 Dec 2017 16:12:15 GMT

Or this

=Count(DISTINCT Aggr(If([Person ID] <> FirstSortedValue(DISTINCT TOTAL<[Position]> [Person ID], -[Start Date]), Position), [Person ID], Position, [Start Date]))

Re: Count based on aggregate date

danimelo1 — Wed, 06 Dec 2017 16:21:06 GMT

Thanks so much Sunny. Just what I was looking for!

topic Re: Count based on aggregate date in App Development

Count based on aggregate date

Re: Count based on aggregate date

Re: Count based on aggregate date

Re: Count based on aggregate date