Chart Expression (Return TestID Where TestDate is Max TestDate)

[#1]

Data (in a Table Box) looks like this:

PersonID	TestFlag	TestID	TestDate
1234		B01D410F-4796-450D	4/1/2010
1234	1	C9B4F060-A6FF-4D3	5/5/2011
1234		37FD7369-075E-4CBC	3/16/2011
1234		F063F7A6-C055-4605	4/14/2011
1234		AC5396D2-2454-4F92	4/29/2011
1234		096F11C7-CEAB-414C	5/11/2011
1234	1	6D02D475-56D8-4A1D	6/6/2011
1234	1	87BBA227-2036-482B	10/17/2011

I'm trying to create a Straight Table that looks like this:

PersonID	TestFlag	MOST RECENT TestID	MOST RECENT TestDate
1234	1	87BBA227-2036-482B	10/17/2011

In other words, please give me the most recent TestID and TestDate where the TestFlag = 1 (by PersonID).

Currently, I have the Dimension as PersonID, and my Expression looks like this:

MaxString(

{$<

TestDate = {"=$(Max(TestDate))"},

TestFlag = {1}

>}

TestID)

But this is not returning anything...

Please help.

[johnw · Accepted solution]

most recent test ID   = firstsortedvalue({<TestFlag*={'1'}>} TestID,-TestDate)

most recent test date = max({<TestFlag*={'1'}>} TestDate)

[#3]

Wow! Thx so much, John! Seems to be working now