topic How to find the closest date? in QlikView

How to find the closest date?

Fri, 30 Oct 2009 03:24:37 GMT

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!

How to find the closest date?

johnw — Fri, 30 Oct 2009 06:13:32 GMT

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))

How to find the closest date?

Fri, 30 Oct 2009 07:30:12 GMT

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

How to find the closest date?

johnw — Fri, 30 Oct 2009 22:14:17 GMT

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

How to find the closest date?

Sat, 31 Oct 2009 00:48:58 GMT

Nevermind, I solved it with the following expression:


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