Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
)))
What is choosenDate? A field? And if so, how are these fields connected?
Try
=Date(FirstSortedValue(date, aggr(fabs(date-choosenDate),date)))
In reality choosenDate is computed from two variables, year and month :
Date#(year&month(mois,00)&01, 'YYYYMMDD')
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
)))