TM1 Forum

Hi,

I want to create a dynamic version Budget Vs Forecast (Budget - Forecast) in TM1. I need it to use in all cubes, reports, views etc. I think that create a measure in every cube is not a good design solution, so is there any way to do an elemento with a formula ? Most cases Which is the best solution to do this ?
Regards,
Ariel

I'm not sure if I fully understand your question but if you need to create a Budget vs Forecast formula it has to be done within a rule on the cube that is defined by your measure dimension. The formula should be similar to the below:

['BudvsFcst'] = N: ['Budget'] - ['Forecast'];

omcleod wrote:I'm not sure if I fully understand your question but if you need to create a Budget vs Forecast formula it has to be done within a rule on the cube that is defined by your measure dimension. The formula should be similar to the below:

['BudvsFcst'] = N: ['Budget'] - ['Forecast'];

I would NEVER EVER want to see a rule like this! As version to version variances are single dimensional addition/subtraction this type of calculation should be handled via a rollup in the version dimension. Then it is immediately available in all cubes where the dimension is used.

Simple consolidations like adding and subtracting are best done with consolidations in the dimension structure rather than in rules. You can have a negative weighting so that the 'BudvsFcst' consolidation has a child 'Budget' with a weighting of 1 and a child of 'Forecast' with a weighting of -1.

As dimensions are reused across cubes it's perfectly fine to have this across your model. It won't be calculated unless the user looks at the value, so it won't add any performance overhead where it's not used.

Martin

The other benefit of putting the calculation in the dimension is that it calculates an order of magnitude faster than if it was in a rule.

First of all thanks for response. We prove with -1 weight and it works. I think its a good idea more than a rule calc. But I think are many restrictions for this kind of calculation for example if I want forecast/budget in order to know the divide relationship ? I don't know how to do it with element weight or how to do it for multiply two members.
Example:

something like this ?
Dimension Version
Elements
% bud vs fore
-budget (/)
-forecast (+)

Only simple consolidations can be done using this method - i.e. adding or subtracting. The only complication you can do is to add weightings, e.g. Thousands could be a consolidation of Dollars with a weighting of 0.001.

For multiplication and division though, you must use rules. For your example you could try
[% Bud vs Fore] = ['Forecast'] \ ['Budget'];
or
[% Bud vs Fore] = ['BudvsFcst'] \ ['Budget'];

where BudvsFcst is a consolidation of Budget - Forecast.

Martin