Page 1 of 1

Feeders - for conversion rule

Posted: Thu Aug 03, 2017 7:50 pm
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.

Re: Feeders - for conversion rule

Posted: Fri Aug 04, 2017 2:28 pm
by Drg
maybe ?
[Currency:'Local Currency'] => [Currency:'USD'];

This implies that all other dimensions of the feeds are one to one

Re: Feeders - for conversion rule

Posted: Fri Aug 04, 2017 3:10 pm
by Analytics123
Can I have this :

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

Re: Feeders - for conversion rule

Posted: Fri Aug 04, 2017 3:24 pm
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

Re: Feeders - for conversion rule

Posted: Fri Aug 04, 2017 4:33 pm
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,

Re: Feeders - for conversion rule

Posted: Fri Aug 04, 2017 5:59 pm
by Wim Gielis
Isn't that the purpose of !Months ? To pass the selection of the user to the exchange rates cube ?

Re: Feeders - for conversion rule

Posted: Fri Aug 04, 2017 7:00 pm
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 .

Re: Feeders - for conversion rule

Posted: Fri Aug 04, 2017 7:03 pm
by Wim Gielis
True. I assume you have a months dimension in that cube so yes, that's the way to do it.

Re: Feeders - for conversion rule

Posted: Fri Aug 04, 2017 7:36 pm
by Analytics123
Thanks Wim !!