Get 6 months Average from Production Forecast table

gadzilla1

Hello all,

I've created a report for finished goods in the Item table. I also need to get the average of the next six months of the Forecast Quantity field in the Production Forecast Entry table, not counting the current month.

Since it is December the average for one month would be for the months January - June. On January 1st, the average should automatically calculate the average for February - July automatically when the report is run.

Can anyone help with that code snippet? I've worked on this quite a bit with limited success. Thanks much for any help.

gadzilla1

Or, if it's easier, does anyone know of any native NAV reports/code that averages X months of data rolling. I could look at that code to get some ideas.

Thanks

Alex_Chow

SETRANGE("Date Filter",CALCDATE('+7M-CM-1D',WORKDATE),CALCDATE('+1M-CM',WORKDATE);
CALCFIELDS("Net Change");
AverageQty := ROUND("Net Change" / 6);

This is assuming you use the item table. But you would change the fields to the production forecaste table.

gadzilla1

Thanks Alex. I created a dataitem from the Production Forecast Entry table and placed the following code in the OnAfterGetRecord trigger but the numbers aren't correct. Can you easily tell where I went wrong?

SETRANGE("Forecast Date",CALCDATE('+7M-CM-1D',WORKDATE),CALCDATE('+1M-CM',WORKDATE));
AverageQty := ROUND("Forecast Quantity" / 6);

Thanks - Chris

Alex_Chow

The code is missing the part to sum up the forecast quantity.

gadzilla1

I was a little lost there...you had 'CALCFIELDS("Net Change");' in your example. Since the "Forecast Quantity" field is not a FlowField that code is not valid.

I have not created a total like this...

Alex_Chow

The code needs to loop through the records and add the values up manually.

Check out how to use the REPEAT loop in this forum or in the C/Side help.

gadzilla1

Thanks much for the reply Alex. I've coded using the REPEAT loop. I've just never had to use this to get an average for a date range.

I may be screwed up with my dataitems too...Right now I have the Item dataitem at the top level with Production Forecast Entry linked by the Item No. Maybe it would be wise to use integer and create record variables?

No worries if there are no replies, you learn more through being stumped.

Alex_Chow

If the code is in a process only report with the dataitem as the forecast entry table, then the process only report is already looping.

Nonetheless, please check out the C/Side Application Designer's guide to gain more familiarity with NAV development.

gadzilla1

Thanks again for the reply. I've read through the App Designers guide but never had to create a report like this one. It doesn't seem to me like a simple report...I may be missing something. I'll keep plugging and will post the report when complete.

gadzilla1

Hi Alex,

I figured out where I was going wrong...the following date filter was suggested:

SETRANGE("Date Filter",CALCDATE('+7M-CM-1D',WORKDATE),CALCDATE('+1M-CM',WORKDATE);

I didn't notice how the start/end dates were...I swapped the CALCDATES and coded something like the following that worked great.

ProdForecastEntry.RESET;
ProdForecastEntry.SETRANGE("Item No.","No.");
ProdForecastEntry.SETRANGE("Forecast Date",CALCDATE('+1M-CM',WORKDATE),CALCDATE('+7M-CM-1D',WORKDATE));
IF ProdForecastEntry.FINDSET THEN BEGIN
REPEAT
AverageQty := AverageQty + ProdForecastEntry."Forecast Quantity"
UNTIL ProdForecastEntry.NEXT = 0;
AverageQtyDivided := AverageQty/6; //etc, etc, etc

I apologize for any confusion and am happy with the end result. Thanks for your help, I will put solved in the title.

Happy New Year! Chris