Get the name for least difference

[lakshmittpd]

Hi all,

I have two date fields A date and B date. I have product and C date fields. Now my requirement in chart is to find the min difference between A and B(have to consider + or - 30 days only) and get the product name. For this product i need to get C Date.

Please help

Thanks,

Lakshmi.

[anthonyj]

Hi,

You could try something like this:

only({$<Product ={"$(=firstsortedvalue(Product, BDate - ADate))"} CDate)

The idea is that the first sorted value will return the Product that has the smallest date range between A & B. This is passed through to the set analysis and the only function returns the CDate for that product.

Regards

Anthony

[lakshmittpd]

Date A	Date B	Date C	fabs(DATEA-DATEB)	Product
2020/11/16	2020/11/30	2024/11/15	14	ABC
2020/11/16	2020/12/02	2024/11/29	16	DEF
2020/11/16	2020/10/21	2024/08/15	26	GHI

This is the sameple data.

I need to get the Product ABC because its the lowest.

[anthonyj · Accepted solution]

Hi @lakshmittpd ,

Thanks for the example. It really helps in coming up with a solution.

To return ABC in Product you can use the firstsortedvalue() function.

firstsortedvalue(Product, fabs([Date B] - [Date A]))

To return the C date for ABC.

date(only({$<Product={"$(=firstsortedvalue(Product, fabs([Date B] - [Date A])))"}>}[Date C]))

Thanks

Anthony