Set analysis between dates and for specific currency

[#1]

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.

[Gysbert_Wassenaar]

I think you should solve this problem in the script with intervalmatch to create a correct association between the exchange rate intervals and the invoice dates. See this blog post.

[#3]

Sorry Gysbert I should of mentioned that I have about 30 fields which will need to follow the same logic like invoices.

Is it to complicated to be done within set analysis?