topic Currency Conversion in QlikView

Currency Conversion

Thu, 16 Dec 2010 15:37:57 GMT

Hello,

I have POs that come into the qlikview application in a given currency (i.e. USD) and I want to show the purchase order price in a different currency (i.e. INR) in my charts and tables. The currecny rate should be at the time when the purchase order was created

I would want to have a box with the different currencies. Once I select one (INR) the application should multiply the USD PO price value by the RATE_FROM_US_MULT_FCTR shown above where currency_cde is equal to INR and PO creation date is between effective_dt and expiration_dt. Can someone please help? I'm new to Qlikview and haven't used any expressions yet.

Currency Conversion

marcel_olmo — Thu, 16 Dec 2010 16:38:09 GMT

Hey, in QV you have an upload option with internet URL's.

It's quite simple, all you need is a currencies web page, and export the current data to your qlikview document.

I've uploaded a short example about what you need. If you want an update, you must reload the document.

Hope it helps.

Currency Conversion

suniljain — Thu, 16 Dec 2010 16:42:57 GMT

There are two step in currency conversion.

1) Maintain currency for data that you want to convert.

2) convert source currency to destination currency. This step is totally dependes on data you have . logic will be build on the same.

Currency Conversion

Thu, 16 Dec 2010 16:49:52 GMT

Hi Sunil,

But how do I write the expressions. for example, I would have to find the PO currency type, then find a curreny type choosen by the user, get the PO creation date, find the conversion factor in that date time for the currency choosen by the user, then multiply by that value retrieved. How do I write this?

Currency Conversion

johnw — Fri, 17 Dec 2010 02:38:46 GMT

Attached is an example of currency conversion. Conversion rates can change daily (change the date to a timestamp if you use a more frequent change of conversion rates). Users can select the currency to display in, and the currency should be displayed with the correct symbol and format. I think that's what you're asking for. It also handles localization of dates and demonstrates forcing a chart to remain in a specific currency regardless of what the user selects.

Currency Conversion

suniljain — Fri, 17 Dec 2010 05:37:19 GMT

Thanx John,

John's example perfectly work if you are maintaining currency data as on date basis. But If you are maintaining currency data in Date range like from this date to that data currency rate fro X currency is Y. in that case you have to use Intervalmatch .

Currency Conversion

Fri, 17 Dec 2010 16:40:40 GMT

Thanks! When the user clicks on an entry in a multibox, is that value stored? For instance, can I add logic like if the user selected EUR, then perform some calculation.

Currency Conversion

johnw — Fri, 17 Dec 2010 18:16:25 GMT


us082940 wrote:Thanks! When the user clicks on an entry in a multibox, is that value stored? For instance, can I add logic like if the user selected EUR, then perform some calculation.

Either I'm not understanding the question or the answer is a trivial "Yes". If you select a value of EUR for Currency, for instance, then the value of Currency is EUR. If you want to use logic based on that, it would look like if(Currency='EUR',something,somethingelse).

Edit: However, in the example I've been careful to avoid this sort of logic. You might use it for the occasional exceptional circumstance, but if you're handling 50 different currencies, you don't want to get in the business of making large if/then/else trees to handle the necessary logic. It's much cleaner and faster to execute if you can handle it within the data model itself.

Currency Conversion

johnw — Fri, 17 Dec 2010 18:21:48 GMT


Sunil Jain wrote:John's example perfectly work if you are maintaining currency data as on date basis. But If you are maintaining currency data in Date range like from this date to that data currency rate fro X currency is Y. in that case you have to use Intervalmatch .

You're right that my example doesn't work if you have conversion rates that apply to a date range. And I was mistaken when I said "change the date to a timestamp if you want more frequent change of conversion rates". In either case you would need to use intervalmatch just like you said.

I should probably update my example to demonstrate that instead of having my transaction dates exactly match my conversion rate dates. On the other hand, it's unnecessary complexity if someone stores a conversion rate per day (or per week, or any other regular period). And that makes me not want to change it. I guess I don't know which case would be more common out in the real world.

Currency Conversion

Wed, 30 Mar 2011 18:05:55 GMT

Hi, it would be very interesting to see your example done with an interval match solution. Our company maintains currency rates monthly but have daily transactions that has to be converted into different currencies based on the monthly updated table.

Appreciate your help and support!

Currency Conversion

johnw — Wed, 30 Mar 2011 19:06:30 GMT

OK, good idea. See attached. There might be a more efficient way than all of those joins, but nothing jumped out at me, and I can't spend much time on it. It seems to work, though.

In your specific case, you could get away with slightly simpler script, because I've assumed that the currency exchange rates can be updated independently and on any date. Since you update all rates monthly, you could get away with only establishing the Date and ToDate fields on the record a single time, where I have to do it separately for both the source currency and the target currency.

Edit: Timestamps were mentioned earlier. The intervalmatch approach should work fine with timestamps. Just change all the dates to timestamps. It should be no different doing the intervalmatch by timestamp than by date.

Edit: Actually, in your specific case, you shouldn't be using intervalmatch. You should use my original approach but replace the Date field with a Month field (at least when doing the match up). It's OK to have a Date field, but you should be picking up the exchange rates using the month field. The script will be faster that way than with the intervalmatch solution. Intervalmatch is really for when your updates to the conversion rates are not on regular intervals.

Re: Currency Conversion

Wed, 28 Jun 2017 05:08:53 GMT

I wish to open the file, but I can't.. 😞

Re: Currency Conversion

Wed, 28 Jun 2017 05:17:04 GMT

I have similar problem, I have report that I already started preparing in qlikview, included cost amount and currency, in that report there is two currency (Dollar and Euro), how to set a default currency to Euro?

these two currency there are different value (3000 rows), how to convert the value of dollar to become euro? Is there any conversation?