I'd like some help on creating a formula to determine daily profit. (Sales - Cost = Profit) I'd like this formula to work in a single cell. We're using NAV 5.0 SP1.
For Sales, I SUM the AMOUNT of the Sales Invoice Line, using a date filter on the Sales Header.
The AMOUNT equals the quantity X price so my sales numbers are always correct.
My dilemma with determining COST is that there isn't a TOTAL COST on the sales line. To determine the cost of the line I must multiply quantity X unit cost, per line, then add the sum of all those for the specified date range.
I've posted my Sales formula. We have 7 locations and I must filter the Department Code on the sales header (who wrote the order) and the Location Code on the sales line (where it was invoiced). If I could determine the COST of the formula below, I could then determine the profit.
=SUM(NL("Sum","Sales Invoice Line","Amount","Location Code",$B7,"Type","Item","Document No.",NL("Filter","Sales Invoice Header","No.","Posting Date",$D$2,"Shortcut Dimension 1 Code",D$6))-(NL("Sum","Sales Cr.Memo Line","Amount","Location Code",$B7,"Type","Item","Document No.",NL("Filter","Sales Cr.Memo Header","No.","Posting Date",$D$2,"Shortcut Dimension 1 Code",D$6))))
Thank you for your help!
Date
Votes
3 comments
-
Jet Reports Historic Posts I'm not sure if this would work or not, but this post here may take you in the direction you are after:
http://community.jetreports.com/viewtopic.php?f=7&t=316
Something like this may work as a base (Check the cost field):=NL("Sum","Sales Invoice Line","=NF(,""Amount"")-NF(,""Cost"")")
I've also been directed by others to look at other tables too, such as Item Ledger Entry or Value Entry - that may help you here (or not).
cheers, Mark -
Jet Reports Historic Posts Getting data from the posted document tables is always tricky as posted documents may be deleted once they're printed.
Have you looked at the Item table, Airplaneguy? There are 2 Flowfields in this table that might just give you what you need in a much easier and faster way.
The fields are called Sales (LCY) and COGS (LCY). The formulas in both fields allow you to Flowfilter on Date, Location code and on Dimension 1. Exactly what you need, isn't it? -
Jet Reports Historic Posts Thanks Markl. That did the trick.
hansfousert, I look forward to digging into the Item table deeper, in the future. We're in our 3rd week of 5.0 SP1 after upgrading from 2.6! So I'm a little busy at the moment…. but eager to learn!
Please sign in to leave a comment.