Need Last Value

[johnca]

Here's an easy one, but to me it's not so easy <sigh>, especially when the boss needs it yesterday.

I have ship data where a widget can ship and be returned and ship again. My issue is, I need to find the last time a widget shipped prior to a certain date (got this part). I was able to add a count number to each record as shown in the table below, but can't seem to eliminate all but the highest counter, or latest date. I was hoping to find something like LastSortedValue, but alas...not to be.

Widget SerNo	Ship Date	Counter
1	1/1/2011	1
1	4/4/2011	2
2	1/2/2011	1
3	3/5/2011	1
4	2/1/2011	1
4	4/1/2011	2
5	1/1/2011	1
5	2/1/2011	2
5	3/1/2011	3
6	3/5/2011	1

I need my data to look like this:

Widget SerNo	Ship Date	Counter
1	4/4/2011	2
2	1/2/2011	1
3	3/5/2011	1
4	4/1/2011	2
5	3/1/2011	3
6	3/5/2011	1

Thanks,

John

[danielrozental · Accepted solution]

You can use firstsortedvalue with a reversed order.

Ship date expression would be, firstsortedvalue([Ship date],-[Ship date]) and counter firstsortedvalue(Counter,-[Ship date]).

[er_mohit]

use this function

-firstsorted value function.

it gives you last value..

[johnca]

I had a feeling it would be FirstSortedValue-something-or-other. I see the '-' sign designates the last record rather than the first record...perfect.

Thanks Daniel

[johnca]

This seems to return a negative value of what I'm seeking, date or counter.