How to find the closest date?

[#1]

I need a function where I can find the most approximate date in the past to one supplied as a parameter. To be more clear, based on an invoiced date, I need to look up the order date with a set analysis function. Since the order date can be anywhere from 1 to 7 days in the past, (and obviously no order number linking both tables) I need to, based on the client number and date of a certain invoice, find the corresponding shipping date. I thought something like firstsortedvalue or max(<{OrderDate={"<$(=InvoiceDate)"}}> OrderDate) would work, but it's not happening. Thanks!

[johnw]

Well, set analysis is in the context of the whole document, not just one row on the table, so that's probably why the expression isn't working. I'm guessing you could do the equivalent IF statement, and it might work since IF statements are in the context of the row:

max(if(OrderDate<InvoiceDate,OrderDate))

[#3]

yes, the shipped date and invoiced date are on separate tables, not on the same row. sorry if it wasn't clear.

[johnw]

I didn't think that mattered if the tables are connected, and they should be by the client number. Maybe I'm missing something.

[#5]

Nevermind, I solved it with the following expression:

Sum({1<OrderDate={"$(=max({1<OrderDate={"<$(=date(InvoiceDate))"}>} OrderDate))"}>} Amount)