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".

       

      Why?

       

      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?

           

          HIC

            • 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:

                   

                  ExchangeRates:

                  Mapping Load distinct client_currency, exchange_rate from [Exchange rates]

                   

                  Currencies:

                  Mapping Load distinct billing_code, client_currency from [projects]

                   

                  Projects:

                  Load * from projects;

                   

                  Activity:

                  Load *,

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

                            from activity;

                   

                  Then you can use sum(activity_revenue/exchange_rate)

                   

                  HIC