FirstSortedValue Help!!

[sujeetsingh]

Hello,

I am stuck up with an expression in straight table:

I have data as below

ID, Date   ,Status1    ,Status2

1 , 2/10/2011, A       , B

1, 3/11/3011  , B   ,     G

1,  4/11/2012,L     ,L

1,  5/12/2012 ,A  ,H

I am using expression as

aggr(FirstSortedValue([Status1], -[RowNo()]), ID) but gives value L but it should give the latest value  A with latest date '5/12/2012'

Thanks

[JonnyPoole]

It seems to accept the distinct even the expression editor flags it. This will give you 'A'

FirstSortedValue( distinct Total <ID>  Status1, -Date)

[sujeetsingh]

I want status value  with the max date().

There is grouping at the table based in ID.

[#4]

Try the below,

FirstSortedValue(Status1,aggr(max({<Status1={'*'}-{''}>}Date),ID),1)

[Peter_Cammaert]

OK, then FirstSortedValue will still be your hero.

See doc below.

OK?

Peter

[Peter_Cammaert]

BTW dates in the year 3011 are still far away. But in the example I copied them anyway in sets for IDs 5 and 9

[sujeetsingh]

Peter thanks, for you all reply.

Actually i have Dimension as First name , Last name which is one to one related to ID.

In that case the expressions gives some wrong data.

[JonnyPoole]

curious... any luck with this expression ?

[sujeetsingh · Accepted solution]

Here is the answer

Re: Expression Help