Hello All,
This is my first post in this community, I am working with Microsoft Dynamics NAV 6.0, JetReports Essentials 2011 and Microsoft Excel 2003. I have only been working with JetReports for a few months…
I am looking to calculate inventory aging based on the following calculation using the item ledger entry table:
Remaining Quantity / Quantity * ( Cost Amount (Actual) + Cost Amount (Expected) )
Filtering for the following:
- Posting Date [$f20]
- Remaining Qty >0
- Location Code [G$19]
I have the following function trying to generate the needed calculation, but it is not matching when comparing against actual information when exporting data from the item ledger entry table.
=NL("Sum","Item Ledger Entry","Remaining Quantity","Location Code",G$19,"Posting Date",$F20,"Remaining Quantity",">0")/NL("Sum","Item Ledger Entry","Quantity","Location Code",G$19,"Posting Date",$F20,"Remaining Quantity",">0")*(NL("Sum","Item Ledger Entry","Cost Amount (Actual)","Location Code",G$19,"Posting Date",$F20,"Remaining Quantity",">0")+NL("Sum","Item Ledger Entry","Cost Amount (Expected)","Location Code",G$19,"Posting Date",$F20,"Remaining Quantity",">0"))
If all inventory is still in stock [Remaining Quantity = Quantity] the result is accurate, however, if only a portion remains [Remaining Qty < Quantity] the result is not accurate.
If anybody could provide an idea on how to correct this issue, it is greatly appreciated.
Thank you,
Nicolai
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi Nicolai,
Is the problem that you want to do that calculation for each row in the Item Ledger Entry table and then sum the result? Getting the sums and then doing the calculation on the sums is usually not the same as doing the calculation for each row and summing the result. This formula would do the calculation for each row and sum the results:=NL("Sum","Item Ledger Entry","=NF(,""Remaining Quantity"")/NF(,""Quantity"")*(NF(,""Cost Amount (Actual)"")+NF(,""Cost Amount (Expected)""))","Location Code",G$19,"Posting Date",$F20,"Remaining Quantity",">0")
Of course I'm guessing this formula might have a problem if the Quantity on a record was 0 since that would cause it to divide by 0, so you might have to modify it like this:=NL("Sum","Item Ledger Entry","=IF(NF(,""Quantity"")=0,0,NF(,""Remaining Quantity"")/NF(,""Quantity"")*(NF(,""Cost Amount (Actual)"")+NF(,""Cost Amount (Expected)"")))","Location Code",G$19,"Posting Date",$F20,"Remaining Quantity",">0")
Is that the result you are looking for?
Regards,
Hughes -
Jet Reports Historic Posts Dear Hughes,
the first suggestion you noted did resolve my problem.
Many thanks!
Nicolai
Please sign in to leave a comment.