Solved: Re: SCRIPT: Calculate Average Purchase Price - Qlik Community

valvesqlik · ‎2022-11-23

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,AvgPurchasePrice
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,AvaragePurchasePrice
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

Aditya_Chitale · ‎2022-11-25

values were missing for PURCHASE line items. Added that in updated qvf.

View solution in original post

Or · ‎2022-11-23

You've largely answered your own question, I think:

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
];

Join

Load ProductCode, WarehouseCode, Sum(TotalPrice) / Sum(Quantity) as AvaragePurchasePrice

Resident STOCK_MOVEMENTS

Group by ProductCode, WarehouseCode;

valvesqlik · ‎2022-11-23

sorry, the calculation must be performed only on purchases, and the result must be applied to the sales lines

Or · ‎2022-11-23

Add:

Where Type = 'PURCHASE'

Aditya_Chitale · ‎2022-11-23

something like this ?

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
];

NoConcatenate

STOCK_MOVEMENTS_2:
load *,
WarehouseCode&Type&ProductCode as %Key
resident STOCK_MOVEMENTS;

left join (STOCK_MOVEMENTS_2)

STOCK_MOVEMENTS_3:
load
WarehouseCode&'SALE'&ProductCode as %Key,
sum(TotalPrice)/sum(Quantity) as AvgPurchasePrice
Resident STOCK_MOVEMENTS group by ProductCode,WarehouseCode;

drop table STOCK_MOVEMENTS;

Regards,

Aditya

valvesqlik · ‎2022-11-24

Thanks for the answer, but unfortunately it doesn't work. In this way, the date is not considered.

The expected results (in the calculated column AvgPurchasePrice) for product code A001 and warehouse M2 are:

on 06/01/2022 (first purchase)
1800 / 100 = 18

on 30/05/2022 (second purchase)
(1800+2470) / (100+95) = 21.897436

on 15/06/2022 (first sale, only purchases should be considered)
(1800+2470) / (100+95) = 21.897436

on 29/07/2022 (third purchase)
(1800+2470+4500) / (100+95+300) = 17.717171

on 03/08/2022 (fourth purchase)
(1800+2470+4500+13800) / (100+95+300+600) = 20.611872

on 22/08/2022 (fifth purchase)
(1800+2470+4500+13800+10200) / (100+95+300+600+600) = 19.333333

on 18/09/2022 (second sale, only purchases should be considered)
(1800+2470+4500+13800+10200) / (100+95+300+600+600) = 19.333333

Aditya_Chitale · ‎2022-11-24

Is this what you wanted ?

STOCK_MOVEMENTS_Temp:
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
];

NoConcatenate

STOCK_MOVEMENTS:
load
Date(Date#(Date,'DD/MM/YYYY'),'DD-MM-YYYY') as Date,
WarehouseCode,
Type,
ProductCode,
Quantity,
UnitPrice,
TotalPrice,
Date(Date#(Date,'DD/MM/YYYY'),'DD-MM-YYYY') & WarehouseCode & Type & ProductCode as %LinkKey
Resident STOCK_MOVEMENTS_Temp;

Drop table STOCK_MOVEMENTS_Temp;

NoConcatenate

STOCK_MOVEMENTS_2:
Load
Date,
WarehouseCode,
Type,
ProductCode
Resident STOCK_MOVEMENTS where Type='SALE';

for i=0 to NoOfRows('STOCK_MOVEMENTS_2')-1

let vSALEDate=Peek('Date',$(i),'STOCK_MOVEMENTS_2');

trace '$(vSALEDate)';

NoConcatenate

QVD_GENERATION_TEMP:
load
'$(vSALEDate)' as Date,
WarehouseCode,
Type,
ProductCode,
TotalPrice,
Quantity
Resident STOCK_MOVEMENTS where Date<'$(vSALEDate)' and Type='PURCHASE';

NoConcatenate

QVD_GENERATION:
Load
Date,
WarehouseCode,
Type,
ProductCode,
sum(TotalPrice)/sum(Quantity) as AvgPurchasePrice
Resident QVD_GENERATION_TEMP group by Date, WarehouseCode, Type, ProductCode;

store QVD_GENERATION into
[lib://TESTPATH/QVD_GENERATION_$(vSALEDate).qvd](qvd);

drop table QVD_GENERATION_TEMP;
drop table QVD_GENERATION;

next i;

drop table STOCK_MOVEMENTS_2;

left join (STOCK_MOVEMENTS)

QVD_GENERATION_FINAL:
load
AvgPurchasePrice,
Date & WarehouseCode & 'SALE' & ProductCode as %LinkKey
from [lib://TESTPATH/QVD_GENERATION_*.qvd](qvd);

This might not be the most effecient way as multiple Date wise qvds are stored and would not advice you to use this method if you have a large data set.

Let me know if it worked for you.

Regards,

Aditya

Aditya_Chitale · ‎2022-11-25

values were missing for PURCHASE line items. Added that in updated qvf.

valvesqlik · ‎2022-11-25

YEEESSS !!!

Thanks!!!

😁😁😁

SCRIPT: Calculate Average Purchase Price

Script