Rolling Forecast Rule

[James]

Hello all,

I am trying to create a rolling forecast where the forecast for a month is the actual value from the previous month.

There is a control cube with the following:
-a rfGlobalVars Dimension which contains ForecastMonth,ForeCastYear,CurrentMonth,and CurrentYear.
- a rfString Dimension

There is a rfRevenueForecast Cube which contains the following dimensions:
-rfService
-ppLineItemCode
-rfScenario (actual or forecast)
-bpmYear
-bpmPeriod (which contains the months) -- I have created an attribute for this dimension called 'MonthVals' which simply numbers months 1-12
-rfMeasure

I am not sure how to go about creating a rule that calls the month/year from the control cube and places the value from the previous months actual into the forecast for the next month. Any helpful tips/suggestions/advice?

Thanks in advance,

James

[Martin Ryan]

Code: Select all

['Measure'] = N: if(!Year@=DB('ControlCube', 'ForecastYear', 'String') & !Month@= DB('ControlCube', 'ForecastMonth'),
DB('ThisCube', !Dim1, !Dim2, !Dim3, if(!Month@='Jan', attrs('Year', !Year, 'Previous'), !Year), attrs('Month', !Month, 'Previous'), 'Actual', 'Measure'), stet);

Note this will only rule a single month's values, it won't force prior months to be actuals, but it should give you a starting point on using a control cube and using attributes to retrieve data from previous months.

Note the feeder will need a "Next" attribute, e.g. attrs('Month', !Month, 'Next');

Martin

[James]

Great! Thanks for the help.