12 Replies Latest reply on Oct 18, 2013 5:27 PM by nicholas.vasilius

    Calculated Field using two data sources

    nicholas.vasilius

      Hi all,

       

      I am trying to translate a number of financial statements from their local currency into USD. I have one data source which holds financial data for a number of companies, which I've called "Financials" in the example attachment. I have a second data source which holds the exchange rates for these companies, which I've called "Rates" in the example. These data sources are linked on "Country" and "Date".

       

      What I need to do is create a calculated field which multiplies the financial data in "Financials" (i.e. Sales) but the exchange rates in "Rates" for all companies outside the USA. This is was I wrote for the calculated field ("SalesInUSD" in the attached workbook):

      IF [Country] = "USA"

      THEN [Sales]

      ELSE [Sales]*[Rates].[Rate]

      END

      There was an error that said I need to use aggregate expression when creating calculated fields from two data sources. However, I don't think this will create the outcome I need. "Financials" is a live SQL Database, which I cannot modify or create views from, and "Rates" is from a separate Access Database. Can someone please help with this?

       

      Thank you in advance!