Find keys from another table by date and quantity

Wed, 26 Jul 2017 16:19:25 GMT

Hi,

I have 3 tables as below mocked examples:

Goods received:

part	DateTime	quantity	unit cost
AB12345	2016-07-23 09:00	100	25.37
AB12345	2016-09-15 20:00	150	28.72
AB12345	2017-11-23 12:00	100	30.25

Stock history:

part	DateTime	On Hand Quantity
AB12345	2016-08-12 12:19	85
AB12345	2017-12-27 15:00	125

Sales:

Part	DateTime	Sale Quantity
AB12345	2016-08-12 12:20	2
AB12345	2017-12-27 15:00	1

I need to calculate the cost price of the item at the point of sale by doing the following and using first in first out logic:

* Work out how many of the good receipt lines are still relevant based on the 'on hand quantity' at point of sale.

* Work out the average cost of the parts in stock based on the quantity of each batch remaining and the unit cost of those items.

i.e.
* For the first sale there are 85 items in stock, these are fulfilled from the goods received on 2016/07/23
so the cost of sale is 25.37* 2 = 50.74

* For the second sale, there are 125 items in stock, 100 of these came from goods received on 2017/11/23, the remaining 25 come from the goods received on 2016/09/15.
The cost price should be ((100*30.25) + (25*28.72))/125

..(3025+718)/125 = 29.944

Any help on this greatly appreciated. I've managed to use our stock table to get as far as this but I'm struggling with how to implement this logic.

Thanks,

Colin.

topic Find keys from another table by date and quantity in QlikView

Find keys from another table by date and quantity