Skip to Content

Rolling Forecast script logic (Average of month's actual to be forecast for remaining month of current year)

Hello Experts,

I have one requirement wherein Actual of month pertaining to Current year to be averaged and is to be posted as a Forecast for remaining months

For Example:

1) Fiscal Period is from April 2014 to March 2015

2) In the below mentioned example current period is 2014.06, hence actual data would be available from 2014.01 to 2014.05. I need to average Actual data from 2014.01 to 2014.05 and post the values to the period 2014.06 to 2014.12.

3) I would be selecting Time period from Data manager package.

The issue i am facing is I am not able to average-out actuals in script logic through providing variable. I have marked the same in red,in below script logic.

Script Logic:

*SELECT(%CURRENTMONTH%,"[LEVEL]",TCMOL_TIME,"[ID]='%TCMOL_TIME_SET%'")

*SELECT(%CURRENTYEAR%,"[YEAR]",TCMOL_TIME,"[ID]='%TCMOL_TIME_SET%'")

*SELECT(%ACTUALMONTHS%,"[ID]",TCMOL_TIME,"[ID]<'%TCMOL_TIME_SET%' AND [YEAR]='%CURRENTYEAR%' AND [LEVEL]='%CURRENTMONTH%'")

*SELECT(%FORECASTMONTHS%,"[ID]",TCMOL_TIME,"[ID]>'%TCMOL_TIME_SET%' AND [YEAR]='%CURRENTYEAR%' AND [LEVEL]='%CURRENTMONTH%'")

*FOR %TT% = %FORECASTMONTHS%

*XDIM_MEMBERSET TCMOL_TIME = %ACTUALMONTHS%

*WHEN TCMOL_TIME

*IS*

*REC(EXPRESSION = %VALUE% / 6,TCMOL_TIME = %TT%)

*ENDWHEN

*NEXT

Regards

Bhagyesh Ravange

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 23, 2014 at 07:19 AM

    Hi Bhagyesh,

    "The issue i am facing is I am not able to average-out actuals in script logic through providing variable. I have marked the same in red,in below script logic." - but what is the issue? You get incorrect result or what?

    The line: *REC(EXPRESSION = %VALUE%/6,TCMOL_TIME = %TT%) is OK

    Vadim

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 23, 2014 at 07:36 AM

    Hi,

    You can maintain a separate property in time to get this value to make this calculation dynamic.

    Hope it helps..

    regards,

    Raju

    Add comment
    10|10000 characters needed characters exceeded