3 Replies Latest reply: Feb 5, 2013 8:44 AM by Henric Cronström RSS

    Problem in aggregating data with different currencies

      Hi all,

      here is my problem:


      I have 3 sources (xls files) with the following fields

      1st has project_code and activity_revenue;

      2nd has project_code, type of work, and currency;

      3rd has currency and exchange_rate.


      In a chart which has as dimension "type of work" and as expression "sum(activity_revenue)/exchange_rate" I get the errore "No Data to visualize".




      If I change the expression in "sum(activity_revenue/exchange_rate)" data is showed but the values are wrong.


      In a table with "project_code" as dimension and "sum(activity_revenue)/exchange_rate" as expression, data is showed correctly.


      Someone can figure this out?


      Thanks a lot!

        • Re: Problem in aggregating data with different currencies
          Henric Cronström

          In principle, the "sum(activity_revenue/exchange_rate)" should be the right one. Then QlikView will assign the exchange rate to each activity_revenue before summing them.


          The expression "sum(activity_revenue)/exchange_rate" will work only if there is just one possible exchange_rate. If there are several, QlikView will evaluate this as "sum(activity_revenue)/Only(exchange_rate)", i.e. the denominator will be NULL.


          However, your data model only has one key: project_code. So if one project_code has several currencies (e.g. different ones for different types of work) then your data model is insufficient. You need a second key to link activity_revenue and exchange_rate.


          Can you post a file?



            • Re: Problem in aggregating data with different currencies

              Thanks Henric,

              I tried to apply what you wrote,

              the problem is that

              sum(activity_revenue/exchange_rate) returns clearly wrong results, and I cant fugure out why (screnshot 2)


              sum(activity_revenue)/exchange_rate as you say, works only if one exchange rate only is possible


              I attached the structure.


              Do you have any idea about how can I handle it?

              Maybe with variables?

              Because I don't have other keys to add...qlv screen 2.pngqlv screen.png

                • Re: Problem in aggregating data with different currencies
                  Henric Cronström

                  I see what happens now. A single billing code can link several times to the same client_currency so the Sum() function will loop over all combinations.


                  Move the exchange rate into the activity table using the following script:



                  Mapping Load distinct client_currency, exchange_rate from [Exchange rates]



                  Mapping Load distinct billing_code, client_currency from [projects]



                  Load * from projects;



                  Load *,

                            Applymap('ExchangeRates',Applymap('Currencies',billing_code),null()) as exchange_rate

                            from activity;


                  Then you can use sum(activity_revenue/exchange_rate)