Problem with FirstSortedValue()

[#1]

Hi All

I am trying to get the count of incidents using the firstsortedvalue. I have filters for Category and the date. Below is the sample data.

Also, I can not have this at the script as the filters are dynamic.

ID	Group ID	Incident Grade	Incident Time	Incident ID	Incident Date	Incident Date Value	Category
1	80364476	A	00:00:00	95023049	41523	9/6/2013	Public
2	80364476	A	12:24:39	95748468	41564	10/17/2013	Public
3	80364476	A	00:00:00	98735114	41733	4/4/2014	Private
4	80364476	A+	00:00:16	104266044	41960	11/17/2014	Public
5	80364476	A+	00:00:18	107288827	42118	4/24/2015	Private
6	80364476	A+	00:00:31	108508060	42327	11/19/2015	Private
7	80364487	B	00:00:00	95023184	41646	1/7/2014	Public
8	80364487	C	12:24:39	95748603	41687	2/17/2014	Public
9	80364487	C	00:00:00	98735249	41856	8/5/2014	Private
10	80364487	B	00:00:16	104266179	42083	3/20/2015	Public
11	80364487	B	00:00:18	107288962	42241	8/25/2015	Private
12	80364487	C	00:00:31	108508195	42450	12/1/2015	Private

If I select Public and date '12/31/2014' I should see

80364476	A+	104266044	11/17/2014	Public
80364487	C	95748603	2/17/2014	Public

If I select just the date '12/31/2014' and nothing from the Category, then

80364476	A+	104266044	11/17/2014	Public
80364487	C	98735249	8/5/2014	Private

Thanks for all the help.

[sunny_talwar]

How are you getting a count = 1? Can you elaborate?

[#3]

Desired result is

Incident Grade	Count
A	1
C	1

I am trying this expression. But it is not giving me the right result.

Count( AGGR(

FirstSortedValue({<[Incident Date Value] = {"$(='<' & Date(Max({AS}[Incident Date Value])))"}, [Incident Date]>}[Incident ID], -[Incident Date])

,[Group ID]))

Thanks

[sunny_talwar]

Seems to be working for me:

[#5]

If I use the expression, i get a different Grade and ID.

Thanks

[sunny_talwar]

What did you remove? the dimension?

[#7]

I have attached the QVW.

[sunny_talwar · Accepted solution]

See if the attached is helpful

[#9]

The grade should be C for 95748603. But it is showing B.

[sunny_talwar]

If you are going to be showing this in a straight table, I would suggest using [Group ID] as your dimension and hide it. and use three expressions: