Crossing Variables with Dimensions

[Anonymous]

Hi all,

I have a file that has two main tables, one is 'Currency Rate' by date, so it show like this:

Date	EUR	USD	CNY	MYR	NOK	CAD	AUD
01/01/2015	9,47	7,84	1,26	2,23	1,04	6,70	6,38
02/01/2015	9,52	7,93	1,28	2,25	1,04	6,72	6,42

And another with 'Invoicing', with many more columns, but one of them is 'Offer Currency' showing which currency was used for each offering.

What I want now is a graph showing the value of all offering in a chosen currency. Currently I've set the variable vCurrency with all posible currencies in a dropdown list and the graph formula is currently like this:

=if ( [Offer Currency]=vCurrency , [Customer end-price] ,

Which is working perfectly, what I don't know is how to make the expression check the currency chosen in the variable and then multiply by the correct rate.

Any ideas on how to do that?

[Peter_Cammaert]

Yes, but that implies adding a few columns and - the most important one - making a limited selection of currencies to which you want to convert. For example EUR, CNY, USD and JPY or something. Then JOIN your conversion rates table to the facts table as many times as you have target currencies. Each JOIN adds a conversion rate from the original currency (can be different for each row) to a target currency.

If you give those embedded conversion factor a field name that contains the target currency, then your ultimate expression for Sales becomes (using names like ConversionFactorToEUR, ConversionFactorToUSD, etc.):

=Sum(AmountInOriginalCurrency * ConversionFactorTo$(vCurrency) )

Why not then calculate amounts in different currencies from the start? Because the JOIN used in this technique can be easily embedded in a loop that walks a series of target currencies (in another variable) and adds the necessary Conversion columns to your facts table. Makes this mechanism extremely flexible. The only thing you need to do for getting additional target currencies is provide the conversion rates and add the currency symbols to your configuration variable.

[Anonymous]

Hi Peter,

I'm sorry, but I'm very new to QlikView. How do I use the JOIN function? Is it in the script?

The conversion will be always from any currency to just one, eg. EUR, CAD, JPY to USD.