topic Convert Multiple Currency Sales to USD in QlikView

Convert Multiple Currency Sales to USD

amit_saini — Tue, 27 Jun 2017 06:52:31 GMT

Hi Folks,

Below is my data set:

Data Table:

Id	Currency	Rate	Sales
a0C3100000hWz9cEAC	BRL	3.1108	$3,274,581.00
a0Ci0000006gMZrEAM	CNY	6.886	$53,726,100.00
a0Ci000000NahCXEAZ	CNY	6.886	$155,849,641.00
a0Ci0000006hAWDEA2	CNY	6.886	$36,332,593.00
a0C3100000j3KEhEAM	EUR	0.951022	$6,159,000.00
a0C3100000j3KHREA2	EUR	0.951022	$25,480,000.00
a0Ci000000NZ1v8EAD	GBP	0.81367	$3,696,902.00
a0C3100000j3PgQEAU	GBP	0.81367	$5,804,945.00
a0Ci000000NZ1f0EAD	GBP	0.81367	$8,297,931.00
a0C3100000hWP9HEAW	GBP	0.81367	$24,671,190.00
a0C3100000hWsBlEAK	USD	1	$1,555,166.00
a0Ci000000NaSSEEA3	USD	1	$2,530,000.00

Exchange Rate Table:

Currency	Rate
BRL	3.1108
CNY	6.886
CNY	6.886
CNY	6.886
EUR	0.951022
EUR	0.951022
GBP	0.81367
GBP	0.81367
GBP	0.81367
GBP	0.81367
USD	1
USD	1

I need to convert all sales to USD and present into final table.

At the moment I'm trying expression something like below:

if(Currency,'GBP',sum(Sales)/Rate,if(Currency,'BRL',sum(Sales)/Rate.....and so on.

But is there any best way of doing exchange rate conversion .

I need all sales figures in USD.

Thanks,

Re: Convert Multiple Currency Sales to USD

niclaz79 — Tue, 27 Jun 2017 07:12:21 GMT

Hi Amit,

Your sales already shows in $ in your table, but I am guessing this is the local currency right?

If your table are as above, just add the following line to your load script:

Sales * Rate as USDSales

The above will take the sales and multiply it by the rate of the same row, and your rows already contain both the rate and local currency sales number.

Re: Convert Multiple Currency Sales to USD

Gysbert_Wassenaar — Tue, 27 Jun 2017 07:13:03 GMT

Looks like you have the rates already in your data table. Why can't you simply divide Sales by Rate?

Re: Convert Multiple Currency Sales to USD

amit_saini — Tue, 27 Jun 2017 07:30:00 GMT

Sorry Gysbert,

This is correct data:

Id	Currency	Sales
a0C3100000hWz9cEAC	BRL	$3,274,581.00
a0Ci0000006gMZrEAM	CNY	$53,726,100.00
a0Ci000000NahCXEAZ	CNY	$155,849,641.00
a0Ci0000006hAWDEA2	CNY	$36,332,593.00
a0C3100000j3KEhEAM	EUR	$6,159,000.00
a0C3100000j3KHREA2	EUR	$25,480,000.00
a0Ci000000NZ1v8EAD	GBP	$3,696,902.00
a0C3100000j3PgQEAU	GBP	$5,804,945.00
a0Ci000000NZ1f0EAD	GBP	$8,297,931.00
a0C3100000hWP9HEAW	GBP	$24,671,190.00
a0C3100000hWsBlEAK	USD	$1,555,166.00
a0Ci000000NaSSEEA3	USD	$2,530,000.00

Rate is coming from other table.

Result need to be a bar chart with Month as Dim and Sale in USD as expression.

My problem is the when I'm doing exchange rate calculation for USD, some of the months are missing :

But if same USD in Million I'm calling in table as below you can see data for Mar and Apr , don't know what's the issue:

Thanks,

Re: Convert Multiple Currency Sales to USD

Kushal_Chawda — Tue, 27 Jun 2017 07:36:26 GMT

If you have link your data with exchange rate table based on currency then below expression should work

=sum(aggr(sum(Sales)/sum( distinct Rate), Currency, Month))

Re: Convert Multiple Currency Sales to USD

niclaz79 — Tue, 27 Jun 2017 07:36:30 GMT

Amit,

If you load the rate exchange table as a map, i.e

mCurrencyExchange:

Mapping LOAD distinct

Currency,

Rate

Resident ExchangeRateTable;

and then apply it to your currencytable with:

Sales * ApplyMap('mCurrencyExchange',Currency) as USDSales

You will get the same result. Be careful though if there are differenct exchange rates for different months etc you need to take that into consideration.

As for the months missing, there is not sufficient information on the app to know why that is.