topic Display last Invoice date only in QlikView

Display last Invoice date only

Sat, 04 Dec 2010 03:40:15 GMT

I am trying to display the last invoice date only for each item in the list below. I have tried variations of the Max function with no luck. Any suggestions on how to show only the last invoice date for each unique item with a null value if there is no last invoice date available? Data table is below:

Item No	Description	Box Ct	UOM	Invoice Date	Disc. Date
					-
2584	PEACHY SCRAP	12	EA	04/03/2009	11/17/2010
9639	RED MAN PLUG	12	EA	01/10/2008	11/17/2010
9639	RED MAN PLUG	12	EA	11/14/2008	11/17/2010
9639	RED MAN PLUG	12	EA	11/21/2008	11/17/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	01/09/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	01/31/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	02/06/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	02/27/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	03/27/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	04/10/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	07/17/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	08/28/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	09/11/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	10/02/2009	11/16/2010
22632	EVEREADY HD 4PK AA BATTERY	1	EA	10/16/2009	11/16/2010
22665	EVEREADY HD 2PK C BATTERY	1	EA	08/01/2008	11/16/2010
22665	EVEREADY HD 2PK C BATTERY	1	EA	12/26/2008	11/16/2010
22665	EVEREADY HD 2PK C BATTERY	1	EA	01/16/2009	11/16/2010
22665	EVEREADY HD 2PK C BATTERY	1	EA	01/31/2009	11/16/2010
22665	EVEREADY HD 2PK C BATTERY	1	EA	02/27/2009	11/16/2010
22665	EVEREADY HD 2PK C BATTERY	1	EA	03/27/2009	11/16/2010
22673	EVEREADY HD 2PK D BATTERY	1	EA	05/30/2008	11/16/2010
22673	EVEREADY HD 2PK D BATTERY	1	EA	07/11/2008	11/16/2010
22673	EVEREADY HD 2PK D BATTERY	1	EA	08/01/2008	11/16/2010
22673	EVEREADY HD 2PK D BATTERY	1	EA	01/02/2009	11/16/2010
22673	EVEREADY HD 2PK D BATTERY	1	EA	01/09/2009	11/16/2010
22673	EVEREADY HD 2PK D BATTERY	1	EA	02/27/2009	11/16/2010
22673	EVEREADY HD 2PK D BATTERY	1	EA	08/28/2009	11/16/2010
24455	EVEREADY HD 9 VOLT BATTERY	1	EA	05/02/2008	11/16/2010
24455	EVEREADY HD 9 VOLT BATTERY	1	EA	09/12/2008	11/16/2010
24455	EVEREADY HD 9 VOLT BATTERY	1	EA	01/31/2009	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	01/04/2008	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	03/07/2008	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	04/18/2008	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	05/30/2008	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	08/01/2008	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	08/29/2008	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	10/17/2008	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	11/27/2008	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	01/02/2009	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	01/16/2009	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	05/08/2009	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	06/19/2009	11/16/2010
56176	EVEREADY HD 4PK AAA BATTERY	1	EA	08/28/2009	11/16/2010
85381	SATHERS CHOC CVRD RAISINS 1.75	12	BG	04/17/2009	11/12/2010
85381	SATHERS CHOC CVRD RAISINS 1.75	12	BG	10/23/2009	11/12/2010

Display last Invoice date only

Sat, 04 Dec 2010 07:36:18 GMT

Use max(date([InvoiceDate])).

Display last Invoice date only

Tue, 07 Dec 2010 17:47:04 GMT

Yeah I've had an incredibly similar problem to this that took forever to figure out (fortunately for you I think mine was much more complicated, but still similar). I'm actually pretty busy at the moment (only here because the expression I have calculating is incredibly slow) so I don't know if I can give you the exact answer, but if you look at the links and my possible solution below and you'll probably get it.

You need to say something like:

if([Invoice Date]=max(total <[Item No]> [Invoice Date]),

[Invoice Date]

)

In the links below they needed the aggr function but I don't think you do since you want to put the dimension (item no) anyway. I guess you'll have to do that same thing I put above except replace the last [Invoice Date] with each field (you'll need 5 expressions). I literally spent 3 minutes on this so there's probably a better way, but hopefully this works and is good enough for you.

http://community.qlik.com/forums/p/37661/148129.aspx#148129

http://community.qlik.com/forums/t/37607.aspx

Display last Invoice date only

Tue, 07 Dec 2010 17:49:53 GMT

Upon further review, 22665 isn't going to show (I don't think) if you do this since the max invoice date is a duplicate. I'm not sure what you want to show in that situation, so I can't really say what I think you should do.

Display last Invoice date only

Tue, 07 Dec 2010 18:12:30 GMT

Hi,
I had a similar 'problem' regarding the last Login Date for Users in our System but "Date(Max(LoginDate),"MMYYDD")" worked for me as per below;: