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

[anbu1984]

=aggr(FirstSortedValue([Status1], -Date), ID)

[Peter_Cammaert]

Why don't you use the value of [Date] as second parameter to FirstSortedValue, instead of RowNo()? IMHO RowNo() will in this case give unpredictable results.

Peter

[#4]

Hi,

Try creating the RowNo inside the script. and then using it in the expression.

Regards

[sujeetsingh]

Do not works

[sujeetsingh]

sorry can not do this in the script

[#7]

Then try,

FirstSortedValue(Status1,aggr(max(Date),ID),1)

[sujeetsingh]

Leave the expressions  ....just what i want is

Straight table with ID as dimension

I want Status1 with latest date aggregated on per ID

[sujeetsingh]

This works ,

But when there is Status1 as null then again a value with latest date then it is taking the one with null value for Status1.

[Peter_Cammaert]

Put ID as dimension in your table, and =max(Date) as expression.

Note that in all cases, including this one, Date has to exist as a Field, not just a column label in your table.