If this question has been answered elsewhere in the forum, I'd appreciate a link. Thanks!
I'd like to calucate in a Jet Report
Item.Inventory > Stockkeeping Unit.Safety Stock Quantity * Stockkeeping Unit.Unit Cost (or Item.Cost)
I'd like to sum this in one field to determine the total value of OVERstock.
Or in other words, only calculate values for items whose inventory is greater than their stockkeeping unit safety stock quantity.
My dilemma is that if I sum all of Stockkeeping Units * Unit Cost, I may be under my target total inventory value, meaning I'm not OVERstocked. But the individual items that I have too many of will get hidden in that request. So I'd just like to focus on the item no. that I have too many of.
At another time, I may like to take this formula to create Rows so I can reveal only the items that I have too many of.
Can someone show me how to do this?
Thank you for your help!
Date
Votes
1 comment
-
Jet Reports Historic Posts Hi,
You could do this with a calculated filter. It might look something like this:=NL("Rows","Item",,"Inventory","="">""&NL(,""Stockkeeping Unit"",""Safety Stock Quantity"",""Item No."",NF(,""No.""))*NL(,""Stockkeeping Unit"",""Unit Cost"",""Item No."",NF(,""No.""))","Link=","Stockkeeping Unit","Item No.","=No.")
So this would just get the sum of the Safety Stock Quantity and Unit Cost for the first entry in the Stockkeeping Unit table for each item. However, you probably have multiple entries in the Stockkeeping Unit table for each item because the Stockkeeping Units are stored by Location and Variant. So you probably would want to add filters within the calculated NL functions to the Stockkeeping Unit table for Location and Variant I'm guessing. Of course you can also easily change this from NL(Rows) to NL(Sum) as well.
I added the Link= so that we are sure we only get items that have an entry in the Stockkeeping Unit table to begin with since it will blow up the calculated filter if we don't. Initially the function actually comes up as #VALUE for me in design mode, but it works once I run it, so be aware that might happen to you as well. Does this help?
Regards,
Hughes
Please sign in to leave a comment.