I am trying to create an item best seller report in Jet 7.1.2 from NAV 5.00 SQL.
My question is how do I filter the list by the results of the Sales ($) flow field in the item card, where it is filtered for a date range and set of locations. I want to rank the report based on sales for a particular time period (eg. a couple of weeks).
While I can sort on Sales ($) by itself, this is not working because the sales cover a wider time period and set of locations than I want to use for the report.
The result I want to use for sorting the list of items is =NF($C12,"Sales ($)","Date Filter",$C$8,"Location Filter",$C$6).
Any help much appreciated.
Date
Votes
6 comments
-
Jet Reports Historic Posts If I understand you correctly, the "Datefilter" and "Location Filter" should be in your NL statement. Something like:
=nl(,"Item",,"-Sales ($)","*","Date Filter",$C$8,"Location Filter",$C$6)
Then associate your NFs to that. -
Jet Reports Historic Posts I should add I work from a heavily customised system, so I may be using fields you do not have. If this is the case, I apologise.
-
Jet Reports Historic Posts Thanks - this worked for me and is very fast. Didn't realise it was that easy!
One technical question - do you know which date the date filter uses? - the document date or the posting date.
Appreciate your assistance. -
Jet Reports Historic Posts I'm lead to believe it is the Posting Date from the corresponding Item Ledger Entry tables, as the Sales ($) field actually references the Item Ledger Entry. I'm not 100% but thats what I believe. Somebody else may be able to advise otherwise.
Whenever I refer to a field like Sales ($) from the Item Card, I always validate the data against something - anything - else to ensure the correct information is being returned. Sometimes simple exports from Nav to Excel will do the trick, or comparing totals to built in reports where possible. -
Jet Reports Historic Posts as mark said… should be the posting date.
You can check this by clicking throug (if possible) on the field in NAV, the filtered records will appear and you can check the filters… -
Jet Reports Historic Posts Thanks for the tip. I have confirmed that the filtering works on posting date not document date.
Please sign in to leave a comment.