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

    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
          Deepak Vadithala

          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
                  Deepak Vadithala

                  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!

                       

                      CurrencyCodeRate
                      DKK5.6162
                      AUD1.072
                      CAD1.0479
                      EUR0.7428
                      GBP0.6233
                      ILS3.528
                      JPY99.736
                      KRW1069.52
                      MXN13.035
                      BRL2.3309
                      CHF0.9158
                      SEK6.6596
                      CNY6.1317
                      ISK123
                      INR63.2587
                      HKD7.7536
                      SGD1.2473
                      OrderIDBillCountryIDSaleOrderDateCurrencyCode
                      45259644575.651/3/2014USD
                      4523836454801/2/2014HKD
                      4523836452501/2/2014HKD
                      4523836453001/2/2014HKD
                      4523836452001/2/2014HKD
                      45302634519.21/5/2014USD
                      45302634578.41/5/2014USD
                      45302634533.61/5/2014USD
                      45302634519.21/5/2014USD
                      453026345141/5/2014USD
                      4525910454281/3/2014HKD
                      45320314530.41/5/2014USD
                      45320314530.41/5/2014USD
                      45320314536.81/5/2014USD
                      45306834517.671/5/2014EUR
                      45306834511.21/5/2014EUR
                      45306834511.21/5/2014EUR
                      45306834511.21/5/2014EUR
                      45306834511.21/5/2014EUR
                      45306834511.21/5/2014EUR
                      453068345491/5/2014EUR