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 |
