topic Re: lookup based off max date value in QlikView

lookup based off max date value

Wed, 17 Sep 2014 17:31:19 GMT

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

Re: lookup based off max date value

JonnyPoole — Wed, 17 Sep 2014 17:49:04 GMT

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 !

Re: lookup based off max date value

maxgro — Wed, 17 Sep 2014 17:49:10 GMT

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]))

Re: lookup based off max date value

JonnyPoole — Wed, 17 Sep 2014 17:51:16 GMT

For only Entry TYpe = Sale, tweak to:

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

Re: lookup based off max date value

arsal_90 — Wed, 17 Sep 2014 18:26:00 GMT

Have Look into the document is that your requirement

Re: lookup based off max date value

Thu, 18 Sep 2014 15:21:45 GMT

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

Re: lookup based off max date value

JonnyPoole — Thu, 18 Sep 2014 15:49:51 GMT

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 ?

Re: lookup based off max date value

Thu, 18 Sep 2014 17:14:55 GMT

This did it. You are the best

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