Demand per Month

[Oliver1]

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!

[sunny_talwar]

@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?

[Oliver1]

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