Currency Conversion

[#1]

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.

[marcel_olmo]

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.

[suniljain]

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.

[#4]

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?

[johnw · Accepted solution]

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.

[suniljain]

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 .

[#7]

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.

[johnw]

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.

[johnw]

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.

[#10]

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!