lookup based off max date value

[#1]

I have a situation where I have to pull a the last sale date and the customer name that is associated with the sale. If I were using excel I would us a MAX then IF VLOOKUP function.

I can get the max sale date from expression below, but I am having difficulty pulling the customer name associated with the last sale date. I am not really sure which kind of function to use (I have tried a lot of different combinations)

max(if(Entry Type]=Sale, [Posting Date],Null()))

Based off the table below I need to get the results Posting date: 08/04/2014 and Customer name: XYZ INC.

Thanks for the help

Item #	Posting date	Entry Type	Customer name
W1	09/04/2014	Transfer
W1	08/04/2014	Sale	XYZ Inc
W1	07/04/2014	Sale	ABC Corp
W1	06/04/2014	Sale	ABC Corp
W1	05/04/2014	Sale	ABC Corp
W1	04/04/2014	Transfer
W1	03/04/2014	Sale	XYZ Inc
W1	02/04/2014	Sale	XYZ Inc
W1	01/04/2014	Transfer

[JonnyPoole]

This expression will get you the customer name associated with the max date:

= FirstSortedValue( [Customer name] , -[Posting date])

Note that the max date in your sample is 9/4/2014 which has '-' for a customer name !

[maxgro]

in a textbox

=concat(DISTINCT {$ <[Posting date]={'$(=date(max({$ <[Entry Type]={Sale}>} [Posting date])))'}   >}    [Customer name],', ')

and posting date

=date(max({$ <[Entry Type]={Sale}>}    [Posting date]))

[JonnyPoole]

For only Entry TYpe = Sale,  tweak to:

= FirstSortedValue(  {$< [Entry Type]={Sale}>}  [Customer name] , -[Posting date])

[arsal_90]

Have Look into the document is that your requirement

[#6]

Thanks Jonathan this is defiantly what I am looking for. The only issue is that my data table (because we track serial numbers) has more than 1 line item per sale per date so I get a null value. If it is just a single line item in my data table there is no problem. I assume that I have to use some kind of Unique or Frist value?

Item #	Posting date	Entry Type	Customer name
W1	09/04/2014	Transfer
W1	08/04/2014	Sale	XYZ Inc
W1	08/04/2014	Sale	XYZ Inc
W1	06/04/2014	Sale	ABC Corp
W1	05/04/2014	Sale	ABC Corp
W1	04/04/2014	Transfer
W1	03/04/2014	Sale	XYZ Inc
W1	02/04/2014	Sale	XYZ Inc
W1	01/04/2014	Transfer

[JonnyPoole]

Hi Garrison - i wasn't able to reproduce the same issue just with the fields above (when i added a duplicated row for the record that is returned by the provided firstsortedvalue expression).

You may need an aggr() or a TOTAL element in the function but i'm not sure without a small sample. Can you share something ?

[#8 · Accepted solution]

This did it. You are the best

FirstSortedValue(DISTINCT [CUSTOMER NAME], if(Entry Type] = 'Sale', -[Posting Date],)))