NetSuite Saved Search Running Total Amount Based On Running Average Cost * Transaction Quantity

I am building a saved search in NetSuite -it is currently a Inventory Detail search- Here is what I am trying to do in a simple sense:

Quantity per transaction = pulled from the transaction

Running total quantity = based on quantity per transaction (add/subtract)

Rate per transaction = pulled from the transaction

Running average cost = calculated by running total amount / running total quantity

Amount per transaction = IF transaction amount = 0 THEN quantity per transaction * running average cost ELSE pull transaction amount

Running total amount = calculated by quantity per transaction * (rate per transaction OR running average cost if rate is null)

I am able to get all of the above fields except for the running total amount because I am utilizing a trick I found online: 'SUM/* comment */(x)...'

Here is what I'm trying and failing at:

SUM/* comment */(CASE WHEN {transaction.amount} = 0 THEN {itemcount} * NVL(ROUND((SUM/* comment */({transaction.amount}) OVER(PARTITION BY {item}    ORDER BY {transaction.datecreated}    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))/(SUM/* comment */({itemcount}) OVER(PARTITION BY {item}    ORDER BY {transaction.datecreated}    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)),5),0) ELSE {transaction.amount} END) OVER(PARTITION BY {item}    ORDER BY {transaction.datecreated}    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

I believe I know the reason its failing; because you can't nest a sum in a sum? But I am not sure of another way, I had the idea of declaring a variable to store the average cost and amount in then call them, but I don't think you can declare in a NetSuite saved search.

Solution

I was unable to accomplish the running total $ amount at the level I was hoping to (inventory detail), however I was able to get it working using a broader approach (transaction) using the following formula:

SUM/* comment */(NVL({creditamount}*-1,0) + NVL({debitamount},0)) OVER(PARTITION BY {item}    ORDER BY {datecreated},{internalid},{linesequencenumber}    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

I think the main issue I had was due to there being too many transacting lines that didn't affect inventory (bin transfers have 2 transacting lines a positive and negative, item fulfillments in a picked/packed status don't take away inventory, etc..). I still believe it may be possible to accomplish this formula on a inventory detail search but I haven't figured it out yet.