how to get this in a pivot table

[ali_hijazi]

Hello

I got a table that contains the following data:

AssessmentID, RecordID, Date,                    Amount

1                      13            20/1/2012             1500

1                      14           20/1/2012              1400

1                      15           21/1/2012              300

1                      16           21/1/2012              250

2                      17           21/1/2012              1500

1                      18           24/1/2012              700

....

Now I have a pivot table with the following dimension:

AssessmentID

now the user selects from the calendar certain date let's say 23/1/2012

what I want in the pivot table is to show foreach assessment id the last recordId whose date is less than 23/1/2012

i.e.

AssessmentID, RecordID        Amount

1                      16                 250

2                      17                 1500

Please advise

[swuehl]

Try Firstsortedvalue with a set expression that limits your Dates, maybe like

=FirstSortedValue({<Date = {"<$(=max(Date))"}>} RecordID, RecordID)

=FirstSortedValue({<Date = {"<$(=max(Date))"}>} Amount, RecordID)

edit: use RecordID instead of Date