topic Re: Daily Rate Currency conversion in QlikView

Daily Rate Currency conversion

Fri, 05 Jun 2015 20:49:40 GMT

I've combed through the forums and haven't quite found the solution Im looking for. I have a DB which receives updated daily exchange rates and stores them, and customers who transact in multiple currencies. I'm trying to figure out how I can use this information in my QV app to allow the selection of the base currency + the date for the exchange rate. I see something similar in the Salesforce app, but my data is different and I was hoping to use it as-is without transformation to fit the salesforce example.

- USER_CONF table contains 'CURRENCY' (ISO code) of the customer and other personal information

- DW_DAILY table contains the customers transactional history I want to convert to the selected currency

- CURRENCY table contains the ISO code, full name of the currency and if its the system wide default

* the above tables all have clear associations

The following two tables have associations to each other but not the rest

- CURRENCY_CONV table contains a CURRENCY_CONV_ID, BASE_CURRENCY and CUR_RATE_DATE

- CURRENCY_CONV_RATE contains the conversion_id, CURRENCY_CONV_ID, TO_CURRENCY (ISO code), currency_rate_date

I would like to build into my app the ability to select the Base Currency 'CURRENCY', and the date of the rate to use 'CURRENCY_RATE_DATE' similar to the way the Salesforce app does, except of course selecting the date of the exchange rate.

Any help is appreciated

Re: Daily Rate Currency conversion

Gysbert_Wassenaar — Mon, 08 Jun 2015 12:52:29 GMT

Can you post a qlikview document that demonstrates the problem?

Re: Daily Rate Currency conversion

Tue, 09 Jun 2015 00:37:47 GMT

Hi Gysbert,

Attached is a sample app, I removed some things to anonymize it a bit.

Thanks in advance for your help!

Grant

Re: Daily Rate Currency conversion

maksim_senin — Tue, 09 Jun 2015 05:20:14 GMT

Hi Grant,

Not sure I understood you task well enough, moreover I'm not familiar with Salesforce, but at first sight it's necessary:

1. To link CURRENCY_CONV table with CURRENCY table via BASE_CURRENCY and CURRENCY fields, correspondingly, by renaming one of them

2. To create expression or variable with calculation of total rate to the end of selected period - this is done by multiplication of exchange rate coefficients in the corresponding period, say:

Exp(Sum({<FILTER PERIOD/DAYS AS YOU NEED>} Log(1+RATE)))-1

Hope this helps,

Best regards,

Maxim

Re: Daily Rate Currency conversion

Gysbert_Wassenaar — Wed, 10 Jun 2015 05:56:09 GMT

See attached qvw.

Re: Daily Rate Currency conversion

Thu, 11 Jun 2015 13:24:35 GMT

Thanks Gysbert and Maxin!, I'll look at this today and let you know if I have anymore questions.