FirstSortedValue - return the max value...

[#1]

Hi all

I am trying to use the FirstSortedValue function - which I think is the correct function to use in my case, but am not able to achieve the correct result. In the example below I am trying to return the latest OrderDate for the max sales date, but based on the max sale value.

As there are three order dates with the same max value of Sale (19.6) without using the 'DISTINCT' syntax the firstsortedvalue does not return a result. However as shown in the yellow box below I wish to return the date of 10/01/2016 (the most recent date that is associated with the max sale amount)  and not the OrderDate of 05/01/2016.

Any ideas are most welcome!

Thanks!

!

[sunny_talwar · Accepted solution]

May be try this:

=FirstSortedValue(DISTINCT OrderDate,-(Sale*100000+OrderDate))

[tresesco]

='Max OrderDate:

FirstSortedValue(DISTINCT OrderDate,Sale): ' & Date(Max({<Sale={"$(=Max(Sale))"}>}OrderDate))