Multiple Currencies in EP

Problem arises when we need multiple currency input and conversion. Let us have 2 currencies: dollars and euros.
Common example — sales cube. Formulas are naive.

  Euro Dollar
Qty    
Price    
Gross Profit    

Price is weighted on Qty as usual

Question: Can data be inputed into multiple currencies at time and then translated into one currency (so called “base”), or only one currency is possible at time?
First variant is much harder, we’ll regard second for start.

I Only currency possible at time.Currency list will be as following:

Dollar -input values
Euro = Dollar * Dollar to Euro Rate
Dollar to Euro Rate - load rates from other cube

After defining such a d-list you can enter either dollars, or euros (which will be converted via breackback).

Storing rates.
A simple cube with Dollar to Euro Rate and all other desired dimensions (timescale, regions, etc) will suite perfectly.

Calculations. We link Dollar to Euro Rate only to cells that ought to be converted(price and margin), and in natural measures (qty) we link 1. That gives us correct non-conversion on qty.
That’s all for first variant.

II Simultaneous multiple currency input.

In the same sales cube we get following problem. If we sell X items for dollars and Y items for euros we need to count not only gross profit in base currency, but also average price of item sold.

Currency list will be as following:

Dollar Input = input values
Euro Input = input values
Dollar to Euro Rate - load rates from other cube
Euro Conv = Euro / Dollar to Euro Rate
Dollar Total = Dollar Input + Euro Conv

So on Dollar Total we get correct average price, because weighting will only work on subtotals.

  • http://www.niteo.com Gaurav Jain

    In practical scenarios, we can have a currency selection for Input currencies and based on that, we can convert data to the base currencies.

    Its slightly round-about but would surely permit using one single model in a multi-currency environment