Creating average forecast

[kudzis]

Hello,

I need help creating a forecast. I need 'Forecast' measure to show actual values (row 'faktas' when flag is 'actual') for actual periods,
and calculate a forecast for forecast periods (when flag is 'forecast'), which is average from actual values (in my example, sum of actual values is 21 from 7 months, so average is 3). In my example, i've filled 'forecast' measure by hand:



I'm new in TM1 rules, what approach should I use? I've started with assigning value from ['faktas', '2011'] to all months in 2011 forecast, but I could not get it work.

Any advice would be greatly appreciated.

[Martin Ryan]

My usual approach for this is to have three scenario dimensions: actual, forecast and blended forecast. I then have an attribute stored against the time dimension if there is only one time dimension as you have, or a lookup cube if there is more than one time dimension - e.g. month and year. In the lookup cube or attribute I have a 1 to indicate whether the period should have actuals.

The rule then looks like this
['Blended'] = N: if(attrn('Time', !Time, 'IsActual')=1, ['Actual'], ['Forecast']);

If I'm using a lookup cube instead of attributes it's pretty similar
['Blended'] = N: if(db('zLookup', !Year, !Month)=1, ['Actual'], ['Forecast']);

You can do something similar here, even if you don't use the three scenario method. The rules and principles are still the same.

HTH,
Martin

[lotsaram]

I would add to Martin's comments that it is both unnecessary and inefficient to store the period flag in the forecast cube itself. Typically you would store information on the status of a period (actualised vs. forecast) in a small lookup cube, that way it is only stored ONCE not potentially thousands upon thousands of times.

Also unless you are using contributor and are forced to make compromises in cube design due to limitations of the interface then it is best to separate string inputs (typically small "assumptions" or "driver" cubes) from numeric input and calculations as separate cubes as this then allows for cube optimisation. (But has no impact on ability to do calculations due to TM1's multi-cube architecture).

[kudzis]

Martin, as I understand, your rule shows fact when flag is set to 'actual', and forecast, when flag is set to 'forecast'. However, I want to calculate forecast - it should be average from fact months (in my example, it's 3). How can I achieve this?

[Martin Ryan]

That's an interesting way of doing a forecast. If I had your set of requirements and the cube structure as you have it I would

1) Create YTD consolidations in the '4 Laikotarpiai' dimension. E.g. '2011.01 YTD' has the child '2011.01'. '2011.03 YTD' has '2011.01', '2011.02, and '2011.03'. You'd need a YTD figure for every single month of the year.
2) I would still have my "Actual" or "Forecast" as an attribute of the Laikotarpiai dimension, rather than in the 'Flag' element.
3) Have a 'Prev' text attribute in the Laikotarpiai dimension that has the previous month. E.g. 2011.02 will have 2011.01 as its 'Prev' attribute.
4) The rule would look something like this

Code: Select all

['Forecast'] = N:
# If actual, then leave for entry
if(attrn('Laikotarpiai', !Laikotarpiai, 'IsActual')=1, stet,
# otherwise it is forecast.  If last month was also a forecast then use that
if(attrn('Laikotarpiai', attrs('Laikotarpiai', !Laikotarpiai, 'Prev'), 'IsActual')=0,
DB('Balansas', !Dim1, !Dim2, !Dim3, attrs('Laikotarpiai', !Laikotarpiai, 'Prev'), 'Forecast'),
# otherwise this month is forecast, but last month is actuals
# so get the sum of actuals for all the months up to last month by using last month's YTD consolidation
DB('Balansas', !Dim1, !Dim2, !Dim3, attrs('Laikotarpiai', !Laikotarpiai, 'Prev') | ' YTD', 'Forecast') \
#  and divide by the number of months in the YTD
elcompn('Laikotarpiai', attrs('Laikotarpiai', !Laikotarpiai, 'Prev') | ' YTD')));

I haven't tested it, there might be a few parentheses that need adding or removing. Also, you'll need to cater for the special case of 20xx.01, where there is no history to use to get the average.

HTH,
Martin