Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2010-12-03 10:40 PM

Display last Invoice date only

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 NoDescriptionBox CtUOMInvoice DateDisc. Date
-
2584PEACHY SCRAP12EA04/03/200911/17/2010
9639RED MAN PLUG12EA01/10/200811/17/2010
9639RED MAN PLUG12EA11/14/200811/17/2010
9639RED MAN PLUG12EA11/21/200811/17/2010
22632EVEREADY HD 4PK AA BATTERY1EA01/09/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA01/31/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA02/06/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA02/27/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA03/27/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA04/10/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA07/17/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA08/28/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA09/11/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA10/02/200911/16/2010
22632EVEREADY HD 4PK AA BATTERY1EA10/16/200911/16/2010
22665EVEREADY HD 2PK C BATTERY1EA08/01/200811/16/2010
22665EVEREADY HD 2PK C BATTERY1EA12/26/200811/16/2010
22665EVEREADY HD 2PK C BATTERY1EA01/16/200911/16/2010
22665EVEREADY HD 2PK C BATTERY1EA01/31/200911/16/2010
22665EVEREADY HD 2PK C BATTERY1EA02/27/200911/16/2010
22665EVEREADY HD 2PK C BATTERY1EA03/27/200911/16/2010
22673EVEREADY HD 2PK D BATTERY1EA05/30/200811/16/2010
22673EVEREADY HD 2PK D BATTERY1EA07/11/200811/16/2010
22673EVEREADY HD 2PK D BATTERY1EA08/01/200811/16/2010
22673EVEREADY HD 2PK D BATTERY1EA01/02/200911/16/2010
22673EVEREADY HD 2PK D BATTERY1EA01/09/200911/16/2010
22673EVEREADY HD 2PK D BATTERY1EA02/27/200911/16/2010
22673EVEREADY HD 2PK D BATTERY1EA08/28/200911/16/2010
24455EVEREADY HD 9 VOLT BATTERY1EA05/02/200811/16/2010
24455EVEREADY HD 9 VOLT BATTERY1EA09/12/200811/16/2010
24455EVEREADY HD 9 VOLT BATTERY1EA01/31/200911/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA01/04/200811/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA03/07/200811/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA04/18/200811/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA05/30/200811/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA08/01/200811/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA08/29/200811/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA10/17/200811/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA11/27/200811/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA01/02/200911/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA01/16/200911/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA05/08/200911/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA06/19/200911/16/2010
56176EVEREADY HD 4PK AAA BATTERY1EA08/28/200911/16/2010
85381SATHERS CHOC CVRD RAISINS 1.7512BG04/17/200911/12/2010
85381SATHERS CHOC CVRD RAISINS 1.7512BG10/23/200911/12/2010


625 Views
0 Likes
4 Replies
Not applicable
‎2010-12-04 02:36 AM
Author

Use max(date([InvoiceDate])).



394 Views
0 Likes
Not applicable
‎2010-12-07 12:47 PM
Author

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

394 Views
0 Likes
Not applicable
‎2010-12-07 12:49 PM
Author

In response to

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.

394 Views
0 Likes
Not applicable
‎2010-12-07 01:12 PM
Author

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;:

394 Views
0 Likes