Greetings!
After some searching within the User Group, I was unable to find a solution that was able to work on this search - so please pardon and direct me to the right place if this question is duplicated elsewhere.
I'm working on a saved search to calculate gross profit by line item, but am running into an issue when taking into account freight revenues and costs. The correct calculation for our gross profit should be:
GP = Product Sales + Freight Invoiced to Customer - COGS - Freight Paid
Using field names, I'm writing it as:
GP = {netamountnotax} + (NVL({shippingamount}, 0)) - (NVL({fulfillingtransaction.cogsamount},0)) - (NVL({custbodycustbody_freight_cost},0))
({custbodycustbody_freight_cost} is a custom field we added to the sales order screen to capture what we pay for freight. It's non-posting, just for searches, reference, etc.
The problem is that when I return these values for each line item in a saved search (Main Line = False), Netsuite pulls the complete {shippingamount} and {custbodycustbody_freight_cost} for each line item. As a result, the shipping revenue and costs are overstated for each order. Obviously, this is only an issue on sales orders with more than one line item. To roughly allocate that revenue and cost amount, I'm trying to divide the {shippingamount} and {custbodycustbody_freight_cost} by the number of line items on the order. It's not 100% accurate, but close enough for our needs.
Where I'm snagging up is the allocation step. The following formula is not working (the bolded items are the areas interest):
Formula (Numeric) / Sum / Round
{netamountnotax} + ({shippingamount} / COUNT({item})) - (NVL({fulfillingtransaction.cogsamount},0)) - ({custbodycustbody_freight_cost} / COUNT({item}))
Can anyone spot what might be the problem? Or have any other solutions to accurately allocate freight charges/costs by line items on order?
Thanks!