topic Re: Demand per Month in App Development

Demand per Month

Oliver1 — Mon, 20 Dec 2021 21:38:40 GMT

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!

Re: Demand per Month

Anil_Babu_Samineni — Thu, 19 Dec 2019 10:11:35 GMT

What is format you are expecting output? PFA

Re: Demand per Month

Oliver1 — Thu, 19 Dec 2019 10:17:14 GMT

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

Re: Demand per Month

Anil_Babu_Samineni — Thu, 19 Dec 2019 10:20:06 GMT

Better, If you could supply the outcome as well?

Re: Demand per Month

Oliver1 — Thu, 19 Dec 2019 10:46:16 GMT

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%

Re: Demand per Month

Anil_Babu_Samineni — Thu, 19 Dec 2019 11:14:19 GMT

What is demand and forecast here?

Re: Demand per Month

Oliver1 — Thu, 19 Dec 2019 11:21:17 GMT

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.

Re: Demand per Month

Anil_Babu_Samineni — Thu, 19 Dec 2019 11:25:09 GMT

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

Re: Demand per Month

Oliver1 — Thu, 19 Dec 2019 11:58:11 GMT

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

Re: Demand per Month

Anil_Babu_Samineni — Thu, 19 Dec 2019 13:33:53 GMT

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

Re: Demand per Month

sunny_talwar — Thu, 19 Dec 2019 13:54:26 GMT

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

Re: Demand per Month

Oliver1 — Thu, 09 Jan 2020 00:02:37 GMT

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