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!

[Anil_Babu_Samineni]

What is format you are expecting output? PFA

[Oliver1]

I also have forecasts for each material group and month and I want to calculate how accurate the forecasts are so in the end the output should be percentage

[Anil_Babu_Samineni]

Better, If you could supply the outcome as well?

[Oliver1]

For example, when my forecast for group A in January is 120 then my forecast accuracy is 92,31 %.

Calculation:

100-((|demand-forecast|)/demand*100)=92,31%

[Anil_Babu_Samineni]

What is demand and forecast here?

[Oliver1]

The demand is for material group per month form the example in my  post and the forecast is from a third table which shows the forecasts for each material group per month.

The result should be the overall forecast accuracy as a percentage.

[Anil_Babu_Samineni]

You may take care the model, I believe it is already ready. If that is the case, expression I would think like

Num(100-((Sum(TOTAL <Month> demand)-Sum(TOTAL <Month> forecast))/Sum(TOTAL <Month> demand)*100),'#,#0%')

Where as Month field calculated from script like

Month(Date(Date#([order date], 'DD.MM.YYYY'),'DD-MMM-YYYY')) as Month

[Anil_Babu_Samineni]

Num(100-((Sum(TOTAL <Month> demand)-Sum(TOTAL <Month> forecast))/Sum(TOTAL <Month> demand)*100),'#,#0%')

Where as Month field calculated from script like

Month(Date(Date#([order date], 'DD.MM.YYYY'),'DD-MMM-YYYY')) as Month

[Oliver1]

Thank you, but I cant import the date as Month, is thera also a solution where i can use something like month(order date)?

[Anil_Babu_Samineni]

I don't think so, But let's take from other suggestion @Clever_Anjos or @sunny_talwar  etc.