topic Re: Currency conversion qlik sense in App Development

Currency conversion qlik sense

Bharathi09 — Mon, 04 Jul 2022 14:16:53 GMT

Hi,

I am developing an application it has field name called as cost currency it has currencies of USD,MXN,CAD

All these should be converted to USD only

Help me to solve this

Thanks

Re: Currency conversion qlik sense

Andrei_Cusnir — Wed, 06 Jul 2022 10:53:01 GMT

Hello,

I assume that you have something like this:

LOAD
"Cost" as "Cost Original",
"Cost Currency" as "Cost Currency"
FROM [...]
(...);

In that use case scenario you can use:

LOAD
Num("Cost"/PICK(Match([Cost Currency], 'USD', 'MXN', 'CAD'), 1, 20.68140, 1.30320), '#,##0.00;-#,##0.00') as "Cost",
'USD' as "Cost Currency"
FROM [...]
(...);

As you can see it has converted all the Cost Currency to appear as USD and then converted the values as per the exchange rate supplied. Match() function will return 1, 2 or 3 value based on the currency and the Pick() function will return 1, 20.68, 1.30 etc. Therefore if Match() returns 1 the Pick() function will return 1. So Cost/1 = Cost which means that the value will not change for all USD currency values. However, if e.g. Match() returns 2, then Pick() will return 20.68, therefore Cost/20.68 will give you the cost value of MXN in USD.

NOTE: Keep in mind that you will have to manually supply the real exchange rate whenever you are going to reload the application and if you have more than 2 currencies that needs to be converted to USD, you will have to find a different approach supplying them. Maybe this article [1] will help you achieve you with further details.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members.

---

[1] https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-use-currency-exchange-rates-in-Qlik-Sense/td-p/1214259?attachment-id=23486

Re: Currency conversion qlik sense

Bharathi09 — Thu, 07 Jul 2022 04:06:48 GMT

Hi I got new more fields in my app now and for each date there is given exchange rate of converting one Currency to other
In that case how Can I convert
Please Help!!

Thanks

Re: Currency conversion qlik sense

Andrei_Cusnir — Thu, 07 Jul 2022 08:13:11 GMT

Hello,

Unfortunately your use case scenario is a bit too complicated so it might not be fully supported by Qlik Sense. You can try the following steps:

Create a EXCEL file with Date, Cost Currency, Cost Currency Exchange Rate
First load the dataset that you have
Iterate though each value in the dataset
Load the EXCEL file and add a filter to match the current checking date with the current currency
if data is loaded, use the exchange rate to calculate the new cost value
If data is not loaded then it means that you don't have the exchange rate for that currency on that date
Delete temp table
Move to next value in the dataset

Additionally:

If you found the exchange rate in step 5, then replace the cost value with the new calculated one and the cost currency with 'USD', however if you haven't found it, then use the current cost value as it is and in cost currency save the string 'NOT FOUND'. This will help you identify any missing exchange rates from your dataset and ensure that you don't have any wrong cost values saved in your new loaded dataset.

I hope that this information is helpful

Re: Currency conversion qlik sense

Bharathi09 — Tue, 12 Jul 2022 11:15:48 GMT

Hi I have now Currency conversion code as USD,CAD,MXN
I also have Exchange rates for jan1st 2022 to May 2022
Can anyone please help me now to convert currencies to USD

Please help me out

Thank you