Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
=aggr(FirstSortedValue([Status1], -Date), ID)
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
Hi,
Try creating the RowNo inside the script. and then using it in the expression.
Regards
Do not works
sorry can not do this in the script
Then try,
FirstSortedValue(Status1,aggr(max(Date),ID),1)
Leave the expressions ....just what i want is
Straight table with ID as dimension
I want Status1 with latest date aggregated on per ID
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.
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.