TM1 Forum

Posted: **Sat Sep 24, 2011 7:45 am**

I have built a revenue planning cube where users enter qty and a rule calculates the value based on pricing. The Reporting currency is EUR but Users will plan at local currency ie USD GBP or EUR. I need to write a rule that will convert the local currency to Eur if the currency is not Eur This is where I have got to so far:

['Value', 'Eur'] = N: IF ((!BMR Plan - Currencies) @= 'GBP', (DB('BMR Plan - Revenue', !BMR Sales - Companies, !BMR Sales - Division, !BMR Plan - Currencies, !BMR Plan - Scenarios, !BMR Plan - Transaction Types, !BMR Sales - GRG Summary, !BMR Plan - Date Summary, !BMR Sales - Customers, !BMR Sales - Products, 'Value') / 1.35), STET);

But it is not working any help would be appreciated.

Posted: **Sat Sep 24, 2011 9:05 am**

You may introduce two attributes for your division dimension. 'currency' (text) and 'exchange' (num), then input currencies and exchange rates in the attribute cube. You rule may look like the one below for all currencies except EUR:

Code: Select all

['value', 'EUR'] = N: IF(DB('}ElementAttributes_BMR Sales - Division',!BMR Sales - Division,'currency') @<> 'EUR', 
                       DB('BMR Plan - Revenue', !BMR Sales - Companies, !BMR Sales - Division, !BMR Plan - Currencies, !BMR Plan - Scenarios, !BMR Plan - Transaction Types, !BMR Sales - GRG Summary, !BMR Plan - Date Summary, !BMR Sales - Customers, !BMR Sales - Products, !BMR - Measures) / 
					   DB('}ElementAttributes_BMR Sales - Division',!BMR Sales - Division,'exchange'), STET);

Of course, your exchange rates will vary with time. Then you need a support cube including time dimension(s). The 2D attribute cube will not be sufficient. You need to adjust the DB() lookup for the exchange rate then.

HTH

TM1 Forum

Rule Issue

Rule Issue

Re: Rule Issue