0

NL("last")

Jet Reports Historic Posts

September 09, 2014 16:34

None

Follow

I feel properly stupid but I cannot get this function to pick up the last of ANYTHING!

I'm trying to get a last sold date for a list of items by using the formula =NL("Last","Item Ledger Entry","Posting Date","Item No.",E4,"Document Type","Sales Shipment",) where E4 is the cell containing the item no.

I've tried more complicated version which sort by posting date or entry number (like this… =NL("Last","Item Ledger Entry","Posting Date","-Posting Date","01/01/2011..","Document Type","Sales Shipment","Item No.",E4)) but I continually get the first date and never anything else.

I'm sure I'm missing something really dumb and basic but I'm at the end of my rope.

Can any of you lovely people help, please?

matthew

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts September 09, 2014 20:14

  Hi Matthew,
  
  You may have first sold an item on 01/01/2011 but you are trying to find the last time this item was sold which may have been last week so you are expecting a date of 09/01/2014, correct?
  
  That is my understanding of what you are trying to return. I think it comes down to how you are thinking of Dates and sorting.
  
  You are trying to get the "last date sold" for an item which in reality is always going to be the day closest to today.
  
  You can do this one of two ways. First, sort in reverse order as you have but use a "First" instead of "Last"
  
  =NL("First","Item Ledger Entry","Posting Date","-Posting Date","01/01/2011..","Document Type","Sales Shipment","Item No.",E4)You are saying, sort the dates from most recent to oldest and bring back the first value which would be the most recent date.
  
  Or you could do the opposite and use the "Last" function but reverse the sort from oldest date to most recent date:
  
  =NL("Last","Item Ledger Entry","Posting Date","+Posting Date","01/01/2011..","Document Type","Sales Shipment","Item No.",E4)In the end, you had the function correct, you just had it sorting in the opposite order that you actually wanted.
  
  If you are ever unsure, you can use the NL(Rows) function to show all the dates and see their order [see attached].

  • sortedDates.xlsx
• 0
  
  Jet Reports Historic Posts September 10, 2014 08:31

  You are right of course! But I'd tried every combination of First/Last +/- I could think of and couldn't get it to work.
  
  Do you want to know the dumb thing I was doing? …… Not hitting refresh.
  
  :oops: :oops: :oops: :oops: :oops:
  
  Thank you so much for your help and sorry to waste your time on my idiocy!
  
  Cheers

• 0
  
  Jet Reports Historic Posts September 10, 2014 17:01

  Not a waste of time. Now other users know two things.
  
  1) How to sort and get pull dates.
  2) The importance of the Refresh button vs the Report button.
  
  :D

• 0
  
  Jet Reports Historic Posts September 11, 2014 11:34

  Hi @all,
  
  but be careful with the "last" or "first" date..! it might depend on the key of your table…
  in your code, you will get the date of the "first"/"last" entryof your filter….
  but there might be no "date" in the key, the jetreport has chosen..
  
  To be sure, you better select a key in your "NL"-function, where a date is part of the key.
  
  That's my opinion… ;)
  
  regards
  jetsetter