Hello,
I am working from a NAV table (general ledger entry) trying to get one field based on the MAX value of another. I need to get the "No." of all entries that have a MAX Posted Date between DATEA and DATEB. I can not figure out how to get the criteria into the query to use the MAX date?? Any ideas?? Each "No." has multiple POSTING DATES. I need a query to return the "No." for all "No.'s" that have a MAX Posing Date between DATEA and DATEB. I will also need to show the MAX DATE that was used in another column. Any assistance appreciated!!
K
Date
Votes
4 comments
-
Jet Reports Historic Posts To be sure I understand you correctly…
You have 3 posts on, say, No. 4300. Posting date on those is May 1st, May 20th and June 3rd.
Running your report with DATEA = May 1st and DATEB = May 31st, you do NOT want to find No. 4300, because the MAX date of the posts is not between the two dates?
Assuming the above is true, I think you can get that with the array functions.
First you need to find all the No.'s that have posts in between the two dates: E6 =NL("Filter","General Ledger Entry","No.","Posting date",DATEA&".."&DATEB)Second you can retrieve all the No.'s that have posts AFTER DATEB: E7 =NL("Filter","General Ledger Entry","No.","Posting date",DATEB+1&"..")What is in the second array should not be in the first (explained here) : E8 =NP("Difference",E6,E7)Now you can generate lines with E9 =NL("Rows",E8)
For readability I used different cells for every function, but it can be one:NL("Rows",NP("Difference",NL("Filter","General Ledger Entry","No.","Posting date",DATEA&".."&DATEB),NL("Filter","General Ledger Entry","No.","Posting date",DATEB+1&"..")))
I did not do any syntax checking, since I typed the formula's in here. So there maybe some tweaking needed…
HTH
rmw -
Jet Reports Historic Posts yes, close. I need to get a list of shipments based on the MAX posting date. Not those with one of several posting dates in the range but the LAST POSTING DATE. So shipments with their last activity in the range of DATEA..DATEB. I hope that makes sense? Thank you very much for your assistance!!
-
Jet Reports Historic Posts Yes, that is what I assumed in my previous answer.
Again: the shipment has 3 posts: May 1st, May 20th and June 3rd.
Running your report with DATEA = May 1st and DATEB = May 31st, you do NOT want to find it, because the MAX date (which is June 3rd) is not between the two dates.
When using DATEA = June 1st and DATEB = June 30th you DO want to find it, because its MAX date is June 3rd (and that is between DATEA and B).
rmw -
Jet Reports Historic Posts yes, you did and you were right. I got it working with a couple little tweeks. Probably just a version issue. THANKS FOR YOUR HELP!! Much appreciated!!
Please sign in to leave a comment.