Calculate Average Purchase Price

[valvesqlik]

Hello, I loaded a table from script, sales and purchase movements.
I would need to add to that table a field (average purchase price) on each row, calculated as total value of the purchase (quantity multiplied by unit price) divided by total quantity purchased, for each ProductCode and WarehouseCode.

This is the table:

STOCK_MOVEMENTS:
LOAD * INLINE [
Date,WarehouseCode,Type,ProductCode,Quantity,UnitPrice,TotalPrice
02/01/2022, M1, PURCHASE, C003, 65, 10, 650,
06/01/2022, M2, PURCHASE, A001, 100, 18, 1800,
16/02/2022, M2, PURCHASE, B002, 200, 10, 2000,
19/03/2022, M1, SALE, C003, 65, 15, 975,
06/04/2022, M2, SALE, B002, 60, 10, 600,
30/05/2022, M2, PURCHASE, A001, 95, 26, 2470,
30/05/2022, M2, PURCHASE, B002, 55, 11, 605,
15/06/2022, M2, SALE, A001, 65, 30, 1950,
27/06/2022, M2, PURCHASE, B002, 150, 15, 2250,
29/07/2022, M2, PURCHASE, A001, 300, 15, 4500,
03/08/2022, M2, PURCHASE, A001, 600, 23, 13800,
12/08/2022, M1, PURCHASE, B002, 300, 15, 4500,
22/08/2022, M2, PURCHASE, A001, 600, 17, 10200,
18/09/2022, M1, SALE, B002, 75, 25, 1875,
18/09/2022 M2, SALE, A001, 200, 25, 5000,
];

This is the formula:

sum(totalprice) / sum(quantity) group by productcode+warehousecode, based on the date of the movement.

This is the result I want to get:

Date, WarehouseCode, Type, ProductCode, Quantity, UnitPrice,TotalPrice,AveragePurchasePrice
02/01/2022, M1, PURCHASE, C003, 65, 10, 650, 10,
06/01/2022, M2, PURCHASE, A001, 100, 18, 1800, 18,
16/02/2022, M2, PURCHASE, B002, 200, 10, 2000, 10,
19/03/2022, M1, SALE, C003, 65, 15, 975, 10,
06/04/2022, M2, SALE, B002, 60, 10, 600, 10,
30/05/2022, M2, PURCHASE, A001, 95, 26, 2470, 21.90
30/05/2022, M2, PURCHASE, B002, 55, 11, 605, 10.21
15/06/2022, M2, SALE, A001, 65, 30, 1950, 21.90
27/06/2022, M2, PURCHASE, B002, 150, 15, 2250, 11.98
29/07/2022, M2, PURCHASE, A001, 300, 15, 4500, 17.71
03/08/2022, M2, PURCHASE, A001, 600, 23, 13800, 20.61
12/08/2022, M1, PURCHASE, B002, 300, 15, 4500, 15
22/08/2022, M2, PURCHASE, A001, 600, 17, 10200, 19.33
18/09/2022, M1, SALE, B002, 75, 25, 1875, 15
18/09/2022 M2, SALE, A001, 200, 25, 5000, 19.33

help pleeeeease !!!!

[Dalton_Ruer]

You don't want to use the + sign to concatenate field values, which I'm assuming you did to avoid synthetic key. Below is how I handled it. Notice that I use a preceeding load statement so that for the STOCK_MOVEMENTS table I concatenate 2 different keys and I do 2 different aggregates. 

STOCK_MOVEMENTS:
Load *,
Date&'_'&WarehouseCode as MovementKey1,
Date&'_'&WarehouseCode&'_'&Type as MovementKey2;
LOAD * INLINE [
Date,WarehouseCode,Type,ProductCode,Quantity,UnitPrice,TotalPrice
02/01/2022, M1, PURCHASE, C003, 65, 10, 650
02/01/2022, M1, PURCHASE, C003, 40, 20, 800
02/01/2022, M1, SALE, C003, 40, 20, 800
06/01/2022, M2, PURCHASE, A001, 100, 18, 1800
16/02/2022, M2, PURCHASE, B002, 200, 10, 2000
19/03/2022, M1, SALE, C003, 65, 15, 975
06/04/2022, M2, SALE, B002, 60, 10, 600
30/05/2022, M2, PURCHASE, A001, 95, 26, 2470
30/05/2022, M2, PURCHASE, B002, 55, 11, 605
15/06/2022, M2, SALE, A001, 65, 30, 1950
27/06/2022, M2, PURCHASE, B002, 150, 15, 2250
29/07/2022, M2, PURCHASE, A001, 300, 15, 4500
03/08/2022, M2, PURCHASE, A001, 600, 23, 13800
12/08/2022, M1, PURCHASE, B002, 300, 15, 4500
22/08/2022, M2, PURCHASE, A001, 600, 17, 10200
18/09/2022, M1, SALE, B002, 75, 25, 1875
18/09/2022, M2, SALE, A001, 200, 25, 5000
];

Aggregates1:
Load
Date & '_'&WarehouseCode as MovementKey1,
Sum(TotalPrice) / Sum(Quantity) as AveragePurchasePrice
Resident STOCK_MOVEMENTS
Group by Date, WarehouseCode; // You can simply comma separate all the fields you wish

Aggregates2:
Load
Date&'_'&WarehouseCode&'_'&Type as MovementKey2,
Num(Sum(TotalPrice) / Sum(Quantity), '#,##0.0') as AveragePurchasePricePerType
Resident STOCK_MOVEMENTS
Group by Date, WarehouseCode, Type; // This time I grouped by date, warehouse code and type so that Purchase and Sale are on different rows

That code ends up yielding the following data model. Notice that I added 2 extra rows to the inline data to force the different average 13.8 for the first date entry. 

You could then create a table object that  displays the information and the associative model will handle showing the right average price based on it's values.