Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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!!
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
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 |
HI,
Add a new Row in your Row in Currency table
USD 1
and use the formula sum(sales/rate)