Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try
Date(FirstSortedValue({<InvoiceDate={'>=$(=MontStart(Today(),-6))}>} DISTINCT InvoiceDate,-InvoiceDate))
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.
Thank You for your help. Distinct was all I needed.
date(FirstSortedValue(Distinct InvoiceDate,-if(InvoiceDate >= monthstart(AddMonths(today(),-6)), InvoiceDate)))