Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

danimelo1
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 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

Tags (1)
1 Solution

Accepted Solutions
MVP
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]))


Capture.PNG

3 Replies
MVP
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
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]))


Capture.PNG

danimelo1
New Contributor III

Re: Count based on aggregate date

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