2 Replies Latest reply: Apr 25, 2013 9:08 AM by David Workman RSS

    Set analysis between dates and for specific currency


      I am hoping someone can help me.


      I have two tables where one gives me a list if invoice, invoice date and amount.




      12346          01/04/2012          100.00


      54896          03/03/2012          500.00


      The other is an exchange rate table, detailing currency code, effective to, from dates, rate




      EUR               01/03/2012     31/03/2012          1.2000

      EUR               01/04/2012     30/04/2012          1.1190


      I have a list box of the currencies, and I am trying to create an expression that multiples the invoice amounts by a rate selected based on the invoice date being between the effective to & from dates within a bar chart displaying the amount


      So in my example above if a user selects EUR then inv 12346 would be 100.00 x 1.1190, inv 54896 = 500.00 x 1.2000.


      I have tried to write the expression myself but I keep getting zero's? Is anyone able to tell me what I am doing wrong?


      Current expression I have:


      sum({$<[BILLINGS Bill Date]={">=cddate1 <= cddate2"},curcode={$(vSelCur)}>}[BILLINGS FeesBilled Amt]*[cdrate])