Hello,
We've recently install JET and are looking to do more with it all the time. I've been asked for something which I cannot work out the easiest / quickest way of completing.
We run Dynamics NAV in a retail environment.
We would like to be able to calculate the total transaction value for any transaction (Transaction Table) that contains a certain item number (Trans. Sales Entry). So rather than knowing the total value of the specific item sold we want the total value of any transaction containing that item including the the value of additional items.
So to me it looks like it the Total Table is the "Transaction" table and the linked one should be the "Trans. Sales Entry" table but i'm not sure of how to make the formula work without every transaction appearing in the sheet - which is time consuming and messy to be honest.
For anyone not in retail, it could be looked at as:
Transaction :- Sales Header
Trans. Sales Entry :- Sales Line
To summarise I'm trying to get a filtered sum from the header table based on the equivalent line table containing an item that we specify.
I've tried every which way I can think of to no effect.
Please can someone shed some light on this please?
Many Thanks
Chris
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi Chris,
which field (or value) do you want to get a SUM from?
regards
jetsetter -
Jet Reports Historic Posts Hi JetSetter,
I'm looking to total the "Amount inc VAT" field from the Sales Header - based on the Sales Lines containing a specific item entry.
Chris -
Jet Reports Historic Posts Chris - will it work to do it something like this?
Cell B5 = Item you are retrieving info for
Cell C5 = Date Range (because I can't NOT include a date range in a request like this :) )
Assuming you understand how the ROWS command works:
=NL("ROWS","SALES LINE","SALES HEADER NO.","ITEM NO.",$B$5,"ORDER DATE",$C$5) <- this would be in D7 to make the following formula work :)
NOTE: I'm using your terms, things are labeled a bit differently in my system - for instance in my system the Sales Header No. is actually the DOCUMENT NO., and when referencing it from the Sales Header table it is simply NO.
now you have the Sales Header number for each transaction that took place during your date range, and included the item in question.
You can use that number to then give you your sum totals for each transaction in another cell (something like this)
=NL("SUM","SALES HEADER","AMOUNT INC VAT","NO.",$D7)
~ Heather -
Jet Reports Historic Posts Hi,
the suggestion from Heather is the way, i would try to go.
there might be others, but i would do it the same way.
At first getting the right Doc.-No. from the line, then sum the Value from the Header, filtered on the Doc.No.
regards
Jetsetter
Please sign in to leave a comment.