1) A currency selector, to select the currency that I want to work in the document.
2) One table with the Exchange Rates between my local currencies and all of possibles work-currencies (in this document).
3) Another table with three columns loaded from an XLS and one calculated expression called "Rated Value".
What I want to know is which expression I have to use to see in the column "Rated Value" the converted value from Value ( in ValueCurrency on ValueDate ) * ExchangeRate (from local currency to selected currency on ValueDate). I left the expresion filled with '??' for an easy location.
I attach the document because I think it helps the explanation of the problem.
You need to model your data differently to achieve the desired currency convertion.
1. The currency Rates table should looks like this:
ValueCurrency ("from")
DisplayCurrency ("to")
From Date
To Date
Conversion Rate
2. Your data needs to be linked to the currency table through the field ValueCurrency, and Date, using INTERVALMATCH.
2a. If you don't want to use INTERVALMATCH, you can simplify the relation if you generate daily rates in your Currency Table, so instead of a single row:
From Jan 1 2012 To Jan 7 2012
you'd have 7 rows with each date listed separately.
If you did that, then you could simply join by Currency and Date. You'd get a small Synthetic key, which is relatively innocent. You can use a concatenated key to avoid it.
3. Use the field "DisplayCurrency" to present on the screen.
4. Remember that you need to supply even the obvious conversion rates from the same currency to the same currency (unless you want to mess with IF conditions everywhere):
From Currency: USD
To Currency : USD
Rate: 1
FInally, with all of it in place, your expression will be:
