Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a delivery positions table with "material id", "order date" and "order quantity". In another table there is also the "materialgroup id" for each material, I want to calculate the avg demand for each month and for each material group.
Example:
material id | order date | order quanity |
001 | 1.1.2019 | 100 |
001 | 5.1.2019 | 30 |
001 | 1.2.2019 | 50 |
002 | 1.1.2019 | 20 |
002 | 10.1.2019 | 100 |
material id | material group |
001 | A |
002 | B |
So in this example i should get:
Group A:
130 for January
50 for February
Group B:
120 in January
Thanks for your help!
@Oliver1 are you saying that you don't have a Month or MonthYear field in the script and you don't want to create one? Is there a reason you don't want to create this field?
Sorry for the delayed response @sunny_talwar !
One Problem is, that the material_group_id is in an extra table.
I have the following tables:
delivery positions:
material id | order date | order quanity |
001 | 1.1.2019 | 100 |
001 | 5.1.2019 | 30 |
001 | 1.2.2019 | 50 |
002 | 1.1.2019 | 20 |
002 | 10.1.2019 | 100 |
material master data
material id | material group |
001 | A |
002 | B |
forecast
material group id | forecast date (MM.YYYY) | forecast quantity |
A | 1.2019 | 150 |
A | 2.2019 | 50 |
B | 1.2019 | 130 |
Now I want to compare the forecast quantity with the order quantity and calculate the forecast accuracy like that for each material group and for each month:
FCA (%) = (1-ABS(order quantity - forecast quantity)/order quantity)*100
So I have the FCA for each material group and for each month and then I want to get the average value.
So in the end there should be an average FCA calculated in a chart function, if it is not necessary i do not want to calculate something in de load editor.
I hope now you can understand my problem and thank you for helping me!
Best regards,
Oliver