Multi-Currency

Joaquin_Lazaro — Thu, 31 May 2012 15:38:43 GMT

Hello:

I've created a QV document with three objectes:

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.

Thank you very much.

Joaquín

Multi-Currency

Oleg_Troyansky — Thu, 31 May 2012 16:29:08 GMT

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:

sum(Value*[Conversion Rate])

topic Multi-Currency in QlikView

Multi-Currency

Multi-Currency