5 Replies Latest reply: Jun 17, 2014 5:06 PM by Goutham Kandukuri

# total sales calculation with different currency

Hi there,

I am creating a dashboard displaying the total sales in US\$ for different countries. Our database has all the currency rate except US (Rate=1). For some country (e.g. China), people purchase products in Hong Kong dollar, USD and EUR for some reason. I am going to use expressions to calculate the total sales but I got stuck. Can anyone help me out? Thank you so much!

Best Regards,

Gloria

• ###### Re: total sales calculation with different currency

Hi Gloria,

You need a currency conversion table which converts all the values into one standard currency. I'd suggest either linking this currency conversion table with main Fact table or you can use Mapping Load & Applymap() to extract the conversion field. Once you have the values in one standard currency then you perform aggregation on this field.

Something like Sum([Standard Currency])

I hope this helps!

Cheers,

DV

www.QlikShare.com

• ###### Re: total sales calculation with different currency

Hi DV,

Thank you for your quick reply. We have a currency conversion table contains the conversion rate into US Dollars. But for US Dollars, we do not have Rate=1 to associate US Dollars converted to US Dollars. So when one country's transactions contains both US dollars and non US dollars (e.g. China), I do not know how to use the expression to calculate. Can you help me out?

Thank you!!

• ###### Re: Re: total sales calculation with different currency

Hi Gloria,

If you already have the conversion table then you need something like this:

IF(Currency = 'USD', [Base Currency Value], [Base Currency ] * [Conversion Rate])

It would be easy if you can post the sample QVW.

Cheers,

DV

www.QlikShare.com

• ###### Re: total sales calculation with different currency

Hi DV,

Please see the below table. The main problem here is there is no Currency Rate for US Dollars. So for countries like China who has multiple currencies including USD, If it is not including USD, it's going to be simple (just use sum(Sale/Rate)). But when it includes USD, I am not sure how to sum the sales up and convert it into US Dollars. Thank you so much!

 CurrencyCode Rate DKK 5.6162 AUD 1.072 CAD 1.0479 EUR 0.7428 GBP 0.6233 ILS 3.528 JPY 99.736 KRW 1069.52 MXN 13.035 BRL 2.3309 CHF 0.9158 SEK 6.6596 CNY 6.1317 ISK 123 INR 63.2587 HKD 7.7536 SGD 1.2473
 OrderID BillCountryID Sale OrderDate CurrencyCode 4525964 45 75.65 1/3/2014 USD 4523836 45 480 1/2/2014 HKD 4523836 45 250 1/2/2014 HKD 4523836 45 300 1/2/2014 HKD 4523836 45 200 1/2/2014 HKD 4530263 45 19.2 1/5/2014 USD 4530263 45 78.4 1/5/2014 USD 4530263 45 33.6 1/5/2014 USD 4530263 45 19.2 1/5/2014 USD 4530263 45 14 1/5/2014 USD 4525910 45 428 1/3/2014 HKD 4532031 45 30.4 1/5/2014 USD 4532031 45 30.4 1/5/2014 USD 4532031 45 36.8 1/5/2014 USD 4530683 45 17.67 1/5/2014 EUR 4530683 45 11.2 1/5/2014 EUR 4530683 45 11.2 1/5/2014 EUR 4530683 45 11.2 1/5/2014 EUR 4530683 45 11.2 1/5/2014 EUR 4530683 45 11.2 1/5/2014 EUR 4530683 45 49 1/5/2014 EUR
• ###### Re: total sales calculation with different currency

HI,