Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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