Select nearest date

[#1]

Hi all, here is my problem :

I have a record of product price associed with the date :

     date     price

What I want to do is to find the product price at some date, and if there isn't a record with this date select the nearest one.

There is the expression I tried to use :

date(FirstSortedValue(distinct date, fabs(date - choosenDate)))

So I order the record by their distance with the choosen date and only take the first one.

Any idea why it doesn't work ?

Thanks

[swuehl]

What is choosenDate? A field? And if so, how are these fields connected?

Try

=Date(FirstSortedValue(date, aggr(fabs(date-choosenDate),date)))

[#3]

In reality choosenDate is computed from two variables, year and month :

Date#(year&month(mois,00)&01, 'YYYYMMDD')

[#4 · Accepted solution]

I managed to get the desired result :

I created a new variable "choosenDate" equal to Date#(year&month(mois,00)&01, 'YYYYMMDD') and used it in the expression as follow

=FirstSortedValue(distinct date, (fabs(date- choosenDate)))