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.

       

      e.g

       

      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

       

      e.g

       

      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])

       

      Thanks,


      Dave.