Set analysis between dates and for specific currency

Thu, 25 Apr 2013 12:18:56 GMT

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.

Re: Set analysis between dates and for specific currency

Gysbert_Wassenaar — Thu, 25 Apr 2013 12:40:07 GMT

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.

Re: Set analysis between dates and for specific currency

Thu, 25 Apr 2013 13:08:23 GMT

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?

topic Re: Set analysis between dates and for specific currency in QlikView

Set analysis between dates and for specific currency

Re: Set analysis between dates and for specific currency

Re: Set analysis between dates and for specific currency