# Qlik Sense App Development

New Contributor III

## Count based on aggregate date

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

Tags (1)
1 Solution

Accepted Solutions
MVP

## Re: Count based on aggregate date

Or this

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

3 Replies
MVP

## Re: Count based on aggregate date

May be you need this?

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

MVP

## Re: Count based on aggregate date

Or this

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

New Contributor III

## Re: Count based on aggregate date

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