0

Year-on-Year Difference in Item Sales

Jet Reports Historic Posts

August 14, 2008 15:42

None

Follow

Hello everyone,

Hope all is well.

I am trying to create a report where, for every item what the difference in sales was between this year and last year.

I know I could use the Sales (Qty.) field along with a Date Filter, however, I wish to apply the following filters:

Item No. Vendor No. Statistics Group (custom) Category (custom) Posting Dates / Date Filter (of sales) Customer No.

I am wondering what tables are the best way to handle this:

Choice 1. My original inclination is to have Item, Sales Invoice Header, Sales Invoice Line as NL's in my worksheet with an options worksheet to apply the SUM function along with filters (and if it is the correct way to do it, then I am happy to do so, and my question no longer needs to be answered :D ).

Choice 2. However, as I was researching, I realised that there is a Cust. Ledger Entry and Item Ledger Entry table that I could also use, but I am a little unsure when to use either of these tables in terms of sales quantities and values. Maybe someone could explain the difference between them, please? And a general rule of thumb as to when and how these should be used?

To compare sales values between items, I am just wondering which table(s) is/are the best in my case?

Thanks, in advance,

Stephen

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts August 14, 2008 18:54

  Some things to keep in mind:
  - sales invoice header and sales invoice line are 2 tables for posted sales invoices. Records can be deleted once the invoice is printed.
  - customer ledger entry only holds customer related data. if you need item related data you should combine them with item ledger entry and value entry.
  - item ledger entry holds customer info - for sales entries the source no. is the customer no.
  
  If you happen to have NAV 4 or NAV 5 you could have a look at the Analysis and reports folder of the Sales menu. You can set up sales analysis reports in NAV for customer / item combinations there.
  
  Hope this helps you a bit further along the road.

• 0
  
  Jet Reports Historic Posts August 18, 2008 09:13

  Hi Hans,
  
  Thanks for the reply.
  
  As far as I am aware, our Sales Invoice documents do not delete records once printed - however our Sales Orders do get deleted once they have been posted, (both Shipped and Invoiced).
  
  The user in question is only interested in how many of each item was sold in a particular date period, and wished to filter based on fields from the item card.
  
  What I need to know is what tables would best suit my needs. I am not overly concerned about credit notes, returns, etc. Just what has been sold of each item for a date period. Also, the user would be expecting to filter based on the current values for the fields on the item card, as these are liable to change throughout the year, e.g. Category, so I am going to have to link the item table in because if the item changed category between this year and last year, and we filter on this field, we need to be able to see the sales for this item for last year also.
  
  From what you are telling me, I think my best bet is the Item Ledger Entry and Item tables, as I am not concerned with Customer information, i.e. I will not be filtering on fields from the Customer table. So, would these two tables be enough to accomplish the task I need? Will I be able to use =NL("Sum","Item Ledger Entry Table"…) and =NL("Rows","Item","No."…) to achieve the sales quantities of items within a Date Filter?
  
  We use Navision 3.01A, with no sign of upgrading to a later version in the near future, hence our investment in Jet Reports.

• 0
  
  Jet Reports Historic Posts August 18, 2008 12:30

  Indeed, these tables are all you need!
  
  In fact, you don't need the item table as you can create rows from the item ledger:
  
  =NL("Rows";"Item Ledger Entry";"Item No.",-various filters-)
  The advantage of doing so is that you will not display items that haven't been sold.
  The disadvantage is that you will not display items that haven't been sold. ;)
  
  To attain the items sold either this year or last year, you can use NP("Union";NL("Filter";..);NL("Filter";..)).
  If you use only the filter of this year, you won't see items that were sold last year but not now for example…
  
  If you need more assistance, just ask :).