topic Re: Select nearest date in QlikView

Select nearest date

Fri, 10 Aug 2012 13:36:14 GMT

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 — Fri, 10 Aug 2012 13:48:37 GMT

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

Try

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

Fri, 10 Aug 2012 14:31:07 GMT

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

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

Mon, 13 Aug 2012 09:43:16 GMT

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