Weighted Average Cost (purchase) for wearehouse

[#1]

I've a problem to show a QV table wich contain warehouse data (this data I've now...):

Date In/Out Q.ty Price

01 Gen. BUY 1pz 1$
02 Gen. BUY 1pz 2$
03 Gen. SELL 1pz 0$
04 Gen. BUY 1pz 10$
05 Gen. SELL 1pz 0$


In the BUY records I've the real price for the purchase. I need to show for the 2 SELL record the WAC (of purchase) cost. So I need to obtain this new table with the calculated column "WAV of purchase":

Date In/Out Q.ty Price WAV of purchase

01 Gen. BUY 1pz 1$ -
02 Gen. BUY 1pz 2$ -
03 Gen. SELL 1pz 0$ 1.50$ ( (1$+2$) / (1pz+1pz)
04 Gen. BUY 1pz 10$ -
05 Gen. SELL 1pz 0$ 4.333333$ ( (1$+2$+10$) / (1pz+1pz+10pz)

The provblem is that the values for the formula are in the previous record. I can't use the "above" because as result I'll need to hide and exclude from this table the "BUY" records and obtain this result

Date In/Out Q.ty Price WAV of purchase

03 Gen. SELL 1pz 0$ 1.50$
05 Gen. SELL 1pz 0$ 4.333333$

Any idea?

Thank's in advance

Teo

[#2]

Approach this in the LOAD script. Use the ORDER BY clause to read the rows in timestamp order. Use peek() to create running totals of inventory and cost. Each SELL row reduces the inventory total, reduces the total cost, and puts a value in the WAV column. Each BUY row adds to cost and inventory.

[hectorgarcia]

Hi jay it's a pleasure to talk to you again...hey i would like you ellaborate a little bit more with a sample of how to do accumulated average in the script,

i woul like to make this accumulations in three differeten categories , product,bank and country by month.

A QVW DOC SAMPLE WILL BE MORE THA APPRECIATED

HECTOR GARCIA