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.
Feeders - for conversion rule
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
-
- 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
maybe ?
[Currency:'Local Currency'] => [Currency:'USD'];
This implies that all other dimensions of the feeds are one to one
[Currency:'Local Currency'] => [Currency:'USD'];
This implies that all other dimensions of the feeds are one to one
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: Feeders - for conversion rule
Can I have this :
[Currency:'Local Currency','MX','Sales Measure':'Budget','FX Rate':'Actual']] => ['MX',Currency:'USD','Sales Measures':'Budget'];
[Currency:'Local Currency','MX','Sales Measure':'Budget','FX Rate':'Actual']] => ['MX',Currency:'USD','Sales Measures':'Budget'];
- Steve Rowe
- Site Admin
- Posts: 2417
- 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
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
(((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
www.infocat.co.uk
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: Feeders - for conversion rule
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,
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,
-
- MVP
- Posts: 3118
- 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
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
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
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: Feeders - for conversion rule
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 .
-
- MVP
- Posts: 3118
- 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
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
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
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: Feeders - for conversion rule
Thanks Wim !!