Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

lookup based off max date value

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 dateEntry TypeCustomer name
W109/04/2014Transfer
W108/04/2014SaleXYZ Inc
W107/04/2014SaleABC Corp
W106/04/2014SaleABC Corp
W105/04/2014SaleABC Corp
W104/04/2014Transfer
W103/04/2014SaleXYZ Inc
W102/04/2014SaleXYZ Inc
W101/04/2014Transfer

1 Solution

Accepted Solutions
Not applicable
Author

This did it. You are the best

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

View solution in original post

7 Replies
JonnyPoole
Employee
Employee

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
MVP
MVP

in a textbox

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

1.png

and posting date

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

JonnyPoole
Employee
Employee

For only Entry TYpe = Sale,  tweak to:

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

arsal_90
Creator III
Creator III

Have Look into the document is that your requirement

Not applicable
Author

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 dateEntry TypeCustomer name
W109/04/2014Transfer
W108/04/2014SaleXYZ Inc
W108/04/2014SaleXYZ Inc
W106/04/2014SaleABC Corp
W105/04/2014SaleABC Corp
W104/04/2014Transfer
W103/04/2014SaleXYZ Inc
W102/04/2014SaleXYZ Inc
W101/04/2014Transfer
JonnyPoole
Employee
Employee

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 ?

Not applicable
Author

This did it. You are the best

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