Dimension only works on frist expression

[#1]

Hi

I am new to QlikView and have a problem with a exchange rate chart. I have a dimension [From Currency] and I have 2 expressions [First Date] [First Rate]. For the [First Date] my return is correct i.e. the first date a rate was maintained, but for [First Rate] it is not returning the rate from the correct date. It seems to find the first date any rate was maintained and then return the rate for that date, when it should find the first date the particular rate was maintained and then return that rate.

Can anyone help please?

Regards Steve

Dimension is [From Currency].

*1* First date is correct =min([Rate Date From])

*2* First Rate incorrect. It finds the first date for any rate and then tries to find a rate for all exchange rates for that date. It should find the first rate per currency and then return that value. =only({$<[Rate Date From]={'$(=min([Rate Date From]))'}>} Rate)

*2* So you can see below that with my current code it found the oldest date that a rate was maintained i.e.01.01.2008 - EUR to EUR 1.0000 and then used that date and returned all other rates maintained for 01.01.2008. There are none so it is null. It should return CHF rate on the 02.01.2008, GBP on the 02.01.2008 etc

[vgutkovsky]

Steve,

Your syntax looks a little off. Try something like this for your 2nd expression:

firstsortedvalue(Rate,[Rate Date From])

That should return the first rate for that currency.

Regards,

[#3 · Accepted solution]

Hi Vlad

Many thanks for your help, I really appreciate it. I was able to get the result I needed from your sample code.

For anyone else that has a similar problem. I ended up using the code below, I had to add distinct as two of my from currencies only had one value on multiple dates and for these it returned null.

To get the first rate: firstsortedvalue(distinct Rate,[Rate Date From])

To get the last rate: firstsortedvalue(distinct Rate,-[Rate Date From])