Rolling forecast - offset 12 (plus one year)

[Former Member]

Hi,

I'm struggling with a script for a rolling forecast.

I want to copy actual data from a time attribute "FCST_SOURCE = Y" to forecast data and offset the time with 12 (meaning one year).

So for example "actual data 2011.01" should become "forecast data 2012.01".

I tried using the tmlv formula, but failed. (*rec(expression=%VALUE%, tmlv(12, %SOURCE)).

So now i have the following script, which works, but is far from optimal.

Is there a simpler way to write this below script ? 🙂

*XDIM_MEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET FORECAST = CONTRACT
*XDIM_MEMBERSET INPUTCURRENCY = EUR
//*XDIM_MEMBERSET TIME = BAS(2011.TOTAL)
*XDIM_MEMBERSET PRODUCT = BAS(ALL_PRODUCT)
*XDIM_MEMBERSET MEASURES = PERIODIC
*XDIM_MEMBERSET ACCOUNT = PL010
*XDIM_FILTER TIME = [TIME].Properties("FCST_SOURCE") = X

//*SELECT(%SOURCE%, [ID], TIME, [FCST_SOURCE] = "X")

*WHEN_REF_DATA = TRANS_DATA

*WHEN TIME.PERIOD
*IS JAN
*FOR %MONTH% = 01
*REC(EXPRESSION = %VALUE%, CATEGORY = FCST1, FORECAST = OFFSET12, TIME = "2012.%MONTH%")
*NEXT
*ENDWHEN

*WHEN TIME.PERIOD
*IS FEB
*FOR %MONTH% = 02
*REC(EXPRESSION = %VALUE%, CATEGORY = FCST1, FORECAST = OFFSET12, TIME = "2012.%MONTH%")
*NEXT
*ENDWHEN

*WHEN TIME.PERIOD
*IS MAR
*FOR %MONTH% = 03
*REC(EXPRESSION = %VALUE%, CATEGORY = FCST1, FORECAST = OFFSET12, TIME = "2012.%MONTH%")
*NEXT
*ENDWHEN

etc.. until monh 12.. 😕

regards

jorgen

[former_member186498 · Accepted solution]

Hi Jørgen,

try *REC(expression=%VALUE%, [TIME].[TMVL(12,%TIME_SET%)]) eventually in combination with a *SELECT statement and XDIM_MEMBERSET TIME ... to set the time.

Kind regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

unfortunately, this sums up actual 1-12 for each %MY_PERIODS%. And in fact it doesn't even do the offset....

So it for period 01 sums up 2011.01-2011.12 etc... and placed it 2011.01......

*SELECT(%SOURCE%, [ID], TIME, [FCST_SOURCE] = "X")
*SELECT(%YEAR%, [ID], PARAMETERS, [PARAMETER_ATTR] = "FORECAST_YEAR")
*SELECT(%ACTIVE_CATEGORY%, [ID], CATEGORY, [ACT_FCST] = "X")
*XDIM_MEMBERSET ANYDIMENSIONNAME AS %SOURCE_FORECAST% = OFFSET12

*WHEN_REF_DATA = TRANS_DATA

*WHEN CATEGORY
*IS Actual

*FOR %MY_PERIODS% = %SOURCE%

*REC(EXPRESSION = %VALUE%, CATEGORY = %ACTIVE_CATEGORY%, FORECAST = %SOURCE_FORECAST%, [TIME].[TMVL(12, %MY_PERIODS%)])

*NEXT
*ENDWHEN
*COMMIT

[Former Member · Accepted solution]

Hi,

Use a select statement to select the first period you want to roll and then use thsi period in your rec statement:

e.g.:

*SELECT(%TIMESET%, "[ID]", TIME, [ID]="2012.JAN")
*SELECT(%YEAR%, "[ID]", TIME, [ID]="2012.TOTAL")
*XDIM_MEMBERSET TIME = BAS(%YEAR%)

*WHEN TIME
   *IS TMVL(0,%TIMESET%) //Jan
         *REC(EXPRESSION=%VALUE%, TIME=TMVL(12,%TIMESET%))

    *IS TMVL(1,%TIMESET%) //Feb
          *REC(EXPRESSION=%VALUE%, TIME=TMVL(13,%TIMESET%))

*ENDWHEN

BR,

Lars

[Former Member · Accepted solution]

Hi Lars,

a simple but effective solution, it did the job and adds a bit more flexibility, since I now can control how many month of rolling forecast they want to do. 🙂

Thank you,

Jørgen