Firstsortedvalue

[#1]

Hello,

I am trying to pick most recent date from Order table by Date and Product, but I have run into a problem when an item appears on multiple lines.

Here's an example when my script fails:

date(FirstSortedValue(InvoiceDate,-if(InvoiceDate >= monthstart(AddMonths(today(),-6)), InvoiceDate)))

Product     InvoiceDate

505ABC     07/08/2016

505ABC     07/08/2016

How do I tell it to pick the most recent or just one from each date.

Thanks

Vibol

[sushil353]

Hi,

Try something like this:

date(FirstSortedValue(Distinct InvoiceDate,-Aggr(Max({<InvoiceDate ={"$(=monthstart(AddMonths(today(),-6)))"} InvoiceDate),InvoiceDate,Product))

for better resolution .. attach some sample data or application

HTH

[antoniotiman]

Try

Date(FirstSortedValue({<InvoiceDate={'>=$(=MontStart(Today(),-6))}>} DISTINCT InvoiceDate,-InvoiceDate))

[tresesco]

If you have same product values multiple times for a date, you could use Distinct keyword like:

FirstSortedValue( Distinct Product, -InvoiceDate)

Note: Use of 'distinct' could give red-lined error message, just ignore that.

[#5]

Thank You for your help.  Distinct was all I needed. 

date(FirstSortedValue(Distinct InvoiceDate,-if(InvoiceDate >= monthstart(AddMonths(today(),-6)), InvoiceDate)))