TM1 Forum

I have 2 cubes with different dimensions and want to move Fx rates from one cube to another

Cube 1 Currency
Dimensions -
1. Country,
2. Time and
3. Rate Type(Month end, Quarterly, moving average etc)


Cube 2 Recalculation
Dimension -
1. Country
2. Time
3. Classification
4. Version
5. Measures (Revenue and expense)
6. Recalculation (Recalculate prior year LC to Current year rates etc, holds the fx rate ) So fx rates * PY LC = prior at current

Logic - I want to transfer different rate types in Currency cube to different measures and versions in the recalculation cube. Eg.

- Revenue for version 1 uses Month End rate
- Expenses for version 1 uses average rates
- revenue for version 2 uses Moving average rates
- expense for version 2 uses month end rates etc

I would like to use dynamic calculations based on lookup cubes or attributes. I am really struggling with this and really need some help.

You can achieve this by writing TM1 Rules

Take a look at the Planning Sample database which comes with TM1. It has got some sample rules written which are getting the Exchange rate from an external cube and is used for calculations. Specifically, see the rules for plan_Report cube.

Hope it helps!

Possible rules without a lookup cube:

['Fx rate','Revenue','Version 1']=N:DB('Currency',!Country,!Time,'Month end');
['Fx rate','Expense','Version 1']=N:DB('Currency',!Country,!Time,'Average');
['Fx rate','Revenue','Version 2']=N:DB('Currency',!Country,!Time,'Moving average');
['Fx rate','Expense','Version 2']=N:DB('Currency',!Country,!Time,'Month end');

A possible lookup cube:
'RateType'
1. Time
2. Version
3. Measures
4. Info (string element 'Info' stores the types 'Month end','Average',etc)

and a possible rule including the lookup cube:
['Fx rate']=N:DB('Currency',!Country,!Time,DB('RateType',!Version,!Measures,'Info'));

Thanks Peter, the simple rule is possible but I have a lot of elements in the measure dimension and a formula for each combination might be an overkill.

I like the lookup one and it works great.

Cheers...

You may add a text attribute to your mesure dimension to identify which currency type to use so that you do not need to repeat the same rule statement for each measure.