Count based off Aggregate First Sorted Value

Tue, 14 Jul 2015 20:21:13 GMT

I am attempting to use an aggregate function combined with the firstsortedvalue function in order to identify the last Person associated to an ID. This formula successfully tells me the latest name associated to the ID according to date:

=aggr(FirstSortedValue(Name, -Date),ID)

I now want to know the count of the number of times that name has been associated to the ID. I can't figure out what formula to use for this Count column. I tried plugging this value into a set analysis and it only works when you filter for one value for ID, otherwise it's 0.

=count({<Name={$(=FirstSortedValue(Name, -Date))}>}Date)

Original Data:

ID	Date	Name
1	7/12/2015	Sarah
1	7/13/2015	John
1	7/14/2015	John
2	7/13/2015	Sarah
3	7/12/2015	Jim
3	7/14/2015	Sarah

Intended Result:

ID	Name "=aggr(FirstSortedValue(Name, -Date),ID)"	Count
1	John	2
2	Sarah	1
3	Sarah	1

Re: Count based off Aggregate First Sorted Value

swuehl — Tue, 14 Jul 2015 21:01:25 GMT

Maybe like

=count(aggr(If(Name= FirstSortedValue(TOTAL<ID> Name, -Date), Name),ID,Name,Date))

topic Count based off Aggregate First Sorted Value in QlikView

Count based off Aggregate First Sorted Value

Re: Count based off Aggregate First Sorted Value