Weighted Average calculation

[DJ55]

Hi Everyone,

I wanted to calculate weighted average cost for Material in below data to show the price  trend by fiscal year.

I have created formula in Qlik

sum((OrderQty * NetPrice*Exch. Rate)/Priceunit_Cal)/sum(OrderQty)

consider priceunit_cal = 1

but I am getting value of 20.24 for FY21 but actual value should be approximately equal to 14.

Can anyone please help with the correct formula.

Fiscal Year	Created on	Material	ordered qty.	Net price	Curr.	X Rate	Price (CAD/kg)
FY21	03/17/2021	7-8	24000	12.6	USD	1.278	16.1028
FY21	03/17/2021	7-8	24000	12.6	USD	1.278	16.1028
FY21	02/08/2021	7-8	24000	12.6	USD	1.278	16.1028
FY21	02/08/2021	7-8	24000	12.6	USD	1.278	16.1028
FY21	04/22/2020	7-8	24000	8	USD	1.4	11.2
FY21	04/22/2020	7-8	24000	8	USD	1.4	11.2
FY21	04/22/2020	7-8	24000	8	USD	1.4	11.2
FY20	02/27/2020	7-8	12000	6.6	USD	1.3104	8.64864
FY20	02/27/2020	7-8	12000	8	USD	1.3104	10.4832
FY20	02/27/2020	7-8	24000	6.6	USD	1.3104	8.64864