Finding First, most recent, and previous

[jcoleman13]

Hello, 

I am trying to take a dataset with IDs, amounts, and dates and find the first, most recent, and previous amounts.  I order by the ID, and the date, and then summarize to get the first and last (most recent), but how do I get the previous... meaning the value before the most recent.  For Instance, ID 8 has 4 different amounts and dates, I want to the first, the last and the one right before the last.  Is there any way to do that that I am missing?

Data:

ID	Amount	Date
1	$50	1/1
1	$25	1/15
1	$100	2/1
3	$20	2/18
3	$50	3/4
4	$20	3/9
5	$4	5/15
8	$10	6/15
7	$15	7/1
8	$30	8/2
7	$20	9/2
8	$20	10/15
8	$100	11/25

What I want to see:

ID	First Amount	Most Recent Amount	Previous Amount
1	$50	$100	$25
3	$20	$50	$20
4	$20	$20	$20
5	$4	$4	$4
7	$15	$20	$15
8	$10	$100	$20

Thanks!

[jrlindem · Accepted solution]

Here's an example workflow that solves your problem.  The only thing I didn't do was reformat your numbers back to include the dollar sign, but a simple formula should take care of that for you.

Here's the workflow (also attached):

Hope this helps you along your way.  Cheers, -Jay

[jcoleman13]

Thank you!!!