Hi all,
I'm doing a calculation on the Sales Invoice Lines (and Cr.Memo) and came into something interesting when validating my reports. Here is my calculation as it stands now:=nl("sum","Sales Invoice Line","Amount","Posting Date",I$30,"Type","Item","Gen. Prod. Posting Group",$G34,"Document No.",nl("Filter","Sales Invoice Header","No.","Customer Type",I$33,"Posting Date",I$30))
This works fine for our 'default' currency, Canadian $. However with any other currency, there is a field called 'Currency Factor' on the Header that needs to be taken into account. i.e. [Amount] / [Currency Factor].
With the amount being on the line, and the currency factor on the header, how can I do this calculation in Jet? I can normally find creative ways to do this kind of thing, but not today.
Date
Votes
8 comments
-
Jet Reports Historic Posts If the number of currencies used and the number of repetitions of this function within your worksheet is not that high, I suppose you could make a list of the currencies, use those in a function in the next column to split totals per currency so you can then safely multiply with a currency factor. Something like:
—
D E F G
4 USD NL(… Currency=$D4 …) USD-factor =$E4*$F4
5 CAD NL(… Currency=$D5 …) 1 =$E5*$F5
6 EUR NL(… Currency=$D6 …) EUR-factor =$E6*$F6
===
=SUM($G4:$G6) -
Jet Reports Historic Posts Thanks for the reply
The complication to this issue is the currency factor is not consistent within each code - the currency factor is different for each month, so a USD in June will be different than USD on July, resulting on a different currency factor. And the date ranges I am summing through are YTD.
I had thought of replacing "Amount" with "=nl(,""Amount"")/nl(,""Currency Factor"")", however I can not reference Currency Factor as its in the header, not the line. -
Jet Reports Historic Posts Just my 2 cents…
Sales invoice line is a table that contains Posted sales invoices. Did you know that NAV allows you to delete Posted sales invoices if they've been printed at least once?
That's why I always recommend to use the ledger entry tables instead. These cannot be deleted - only compressed.
And you know what… the fields you need are in the Item ledger entry / Value entry table, i.e. the posting date, the posting group and the amount in LCY! So you no longer need to combine tables - isn't that nice ;) -
Jet Reports Historic Posts Hadn't thought of that…
Also, if you're saying YTD, you're probably not very well off using a helper sheet that just lists all (i.e. col.D=NL("Rows", … local currency … for the duration of which the currency factor is valid); col.E then retrieves the currency factor based on col.D after which col.F (summed at the end) has the normalized amount)
Edit: idd you're not - follow Hans instead ;) -
Jet Reports Historic Posts Thanks for the great suggestions guys. I'll have a go at them and see what I can come up with.
-
Jet Reports Historic Posts When it comes to currencies… to make it more complicated :?
NAV uses the exchange rates from a currency at the moment you create a new transaction to calculate a Currency factor in a transaction. Depending on the exchange rate setup you may be allowed to change the exchange for that specific transaction. So, in theory, each transaction could have a differenct currency factor. -
Jet Reports Historic Posts I'm looking at the Item Ledger Entry and Value Entry tables.
The complication with my reporting requirements is I'm filtering on a custom field called 'Customer Type' which is only present on the Sales Invoice/Cr.Memo Header. (we don't delete invoices or CrMemos).
I'm a little confused as to which columns in these two tables I should be looking at to get a single currency figure. I need to get the Sales amount and Cost (so I can also calculate profit)
In Item Ledger Entry, for costs, there are:
Cost Amount (Expected)
Cost Amount (Actual)
Cost Amount (Expected) (ACY)
Cost Amount (Actual) (ACY)
I was expecting to see LCY not ACY. What does ACY stand for?
Then there is Sales Amount (expected) and Sales Amount (Actual), yet no LCY or ACY values. Are these figures in our local currency? -
Jet Reports Historic Posts There is so much more to learn about NAV if you invest a little time in the NAV Help. ;)
LCY = Local currency
ACY = Additional reporting currency
(both are setup in General ledger setup - LCY is obliged, ACY not)
If the field name does not refer to ACY then the value automatically is in LCY.
From the NAV help:Expected costs are the estimate that you make of the cost of, for example, purchasing an item before you actually receive the invoice for the item.
You can post expected cost to both inventory and to G/L. Whenever you post a document, such as an order or a journal, as received or shipped, a value entry line will be created with the expected cost. This expected cost will affect inventory value, but it will not be posted to G/L unless you have set the program up to do that.
Actual is when the invoice is posted.
Please sign in to leave a comment.