How to get value from the latest date

[#1]

Hello - I'm new user. I was looking for  a solution in other discussions but with poor results. I have one table with average cost history.

Item number	Trans date	Transe time	New avg cost
A	20120620	132632	172
A	20120621	135206	172
A	20120627	101107	175
B	20120620	132632	111
B	20120621	135207	111
B	20120627	101107	110
C	20120620	132632	305
C	20120621	135207	305
C	20120627	101107	309

For each item number I want take 'New avg cost' value for the latest date (and if there are few rows with same item and date - latest transaction time). For this example result would be:

A - 175

B - 110

C - 309

I have tried aggr functions but as a result I have max value.

Thanks in advance.

Best regards from Poland

Paweł

[Gysbert_Wassenaar]

Use the firstsortedvalue function: firstsortedvalue([New avg cost], -[Trans date])

Note, this only works if there is only one record (per Item number) with the maximum date. In your sample data this is the case, so it will work on that data.

edit: I see that Transe time should also be considered. Try firstsortedvalue([New avg cost], -[Trans date]&[Transe time])

[#3]

Thanks for fast response. Unfortunatelly there are many records with same (max) date (per item number).

[#4 · Accepted solution]

Can't you just concatenate your transaction date and time to get a unique value? Are there so many transactions that you will get a substantial number of transactions occuring on the same second?

I had this problem with our contact management system (which only has standard output yy/mm/dd hh:mm) until I found that there was a way to extract the actual seconds value from the record ID. I used a "LOAD" statement to create a new field I called "FullTime" from the combination of Date, Time, and Seconds, and then was able to use FirstSortedValue(FullTime) to find distinct entries.

[#5]

Finally I have managed to do this with your help guys!!! Thanks a lot. So in a script there is:

Trans_date&if(LEN(Trans_time)<6,'0')&Trans_time AS FullTime

And in table:

=firstsortedvalue(New avg cost, -FullTime)

Thank you one more time.