Feeders - for conversion rule

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Feeders - for conversion rule

Post by Analytics123 »

Hi ,

Cube has data loaded in local currency for Mexico and USA .

For budget alone, we wanted Mexico 's value to be converted to USD .so that we can see budget in usd as a whole .

SO I am writing the below rule :
['MX',Currency:'USD', 'Sales Measures': 'Budget','FX Rate':'Actual'] = N: ['MX',Currency:'Local Currency', 'Sales Measures': 'Budget'] *
(((DB('Exchange Rates', 'Actual', !Currencies, !Currency, 'Ending Rate', !Months))+(DB('Exchange Rates', 'Actual', !Currencies, !Currency, 'Ending Rate', ATTRS('Months', !Months, 'Prior'))))/2);


This for Mexico country and usd currency and budget measure does the following calculation

takes the value of budget in local currency and multiplies it by average of the month selected and prior month dollar converted rate .

Everything looks good and works fine .


I am not sure of the feeders here :

is it just the current one we have

[Currency:'Input Currency'] => [Currency:'USD'];

or should i be more specific here , the above one works but I know there is definetely a better way than this.
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Drg »

maybe ?
[Currency:'Local Currency'] => [Currency:'USD'];

This implies that all other dimensions of the feeds are one to one
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Analytics123 »

Can I have this :

[Currency:'Local Currency','MX','Sales Measure':'Budget','FX Rate':'Actual']] => ['MX',Currency:'USD','Sales Measures':'Budget'];
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Feeders - for conversion rule

Post by Steve Rowe »

For speed efficiency consider putting an additional measure in your exchange rate cube to calculate the average

(((DB('Exchange Rates', 'Actual', !Currencies, !Currency, 'Ending Rate', !Months))+(DB('Exchange Rates', 'Actual', !Currencies, !Currency, 'Ending Rate', ATTRS('Months', !Months, 'Prior'))))/2);

Currently you are calculating and not storing the results of the above for many data points, just doing broadly the same work over and over again.

If you calculated this average against a ruled measure in the exchange rate cube then
1. The first time this value is requested the result will be cached.
2. You will be doing this (albeit simple) piece of maths many many fewer times.
3. Results should calculate faster.

Note RAM consumption may go up a little as you are now caching more numbers but I would not expect it to be too noticeable.

For feeders, you can just swap the two square bracket references (probably!)

['MX',Currency:'Local Currency', 'Sales Measures': 'Budget'] => ['MX',Currency:'USD', 'Sales Measures': 'Budget','FX Rate':'Actual'] ;

DRG is right though, it would be normal to just have all of Local Currency feed USD since all the Local Currency will eventually be converted into USD If different parts of it are done by different rules there is no reason to split the feeder up as will.

HTH
Technical Director
www.infocat.co.uk
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Analytics123 »

Yeah thanks , really appreciate your suggestions .

But the average rate is not always on the same months , its done on the month the user selects and its prior month . How would I calculate this as a calculated measure in the exchange rates cube .

I cannot say do an average of jun and July . It has to be the dynamic months the user requests for right ?

Thanks,
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Feeders - for conversion rule

Post by Wim Gielis »

Isn't that the purpose of !Months ? To pass the selection of the user to the exchange rates cube ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Analytics123 »

yes oh i got it , so i will write a rule in exchange rates cube to pick the month and prior months average ending rate and store it as a calculated measure .
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Feeders - for conversion rule

Post by Wim Gielis »

True. I assume you have a months dimension in that cube so yes, that's the way to do it.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Analytics123 »

Thanks Wim !!
Post Reply