5 Replies Latest reply: Oct 7, 2016 12:44 PM by Sunny Talwar RSS

    Set Analysis

    Chris Johnson

      I've linked a list of closing balances to a set of currency exchanges rates using the date of the balance and the MatchInterval of when the conversion rate was valid. I have multiple currencies in both fields so want to return when both currencies match and apply the correct exchange rate.

      sum( {$ <BalanceAutoNumber={"=BalanceCurrency=Currency"}>} ClosingBalance*CADMultiply) 

      As per an online guide, I have added the field BalanceAutoNumber to the Balances table as my dimension on which to select on. However, I'm only getting results when there I manually select in both fields.

      Data looks something like this

      Balances

      BalanceAutoNumberDateForCurrencyCurrencyClosingBalance
      129/10/2015CAD500
      229/10/2015GBP100
      329/10/2015USD200

       

      CurrencyRates

      StartDateEndDateCurrencyCADMultiply
      01/10/201531/10/2015CAD1
      01/10/201531/10/2015GBP1.5
      01/10/201531/10/2015USD1.2
      01/10/201531/10/2015JPY1000

       

      The tables are linked using interval match between DateForCurrency, StartDate and EndDate