Getting the most recent transaction

[jonathandienst]

Hi

I am having difficulty getting the most recent value of of 'BalanceRemaining' from a single table containing DealCode (key field), ValueDate and BalanceRemaining (among other fields of no importance here).

I have a straight table using DealCode as dimension, and the following expressions (bear in mind that these expressions are for testing purposes). One deal is currently selected.

1. Max(ValueDate) - works as expected giving the date for the most recent transaction for the deal 2010/08/31
2. MonthStart(Max(ValueDate)) - works as expected giving 2010/08/01
3. MonthStart(Column(1)) - works as expected giving 2010/08/01
4. Count(If(ValueDate =Date( '2010/08/31'), BalanceRemaining)) - works as expected giving 1 (never more than 1 per day)
5. Count(If(ValueDate = Date(Column(1)), BalanceRemaining)) - does NOT work (returns 0, should be 1)
6. Count(If(ValueDate = Date(Max(ValueDate))), BalanceRemaining)) - does NOT work (returns NULL)

NOTE: when I have finished testing I will change the Count to Only to get the value of the BalanceRemaining field.

My two questions are:

1. What is the correct expression to get the BalanceRemaining for the last ValueDate for a DealCode (which is the table dimension)?
2. Why do expression 2, 3 & 4 above work, but not expressions 5 & 6. Especially seeing as expression 4 has the same syntax as 5 & 6.?

Many thanks

Jonathan

[jonathandienst]

(Bump) Anyone?

[#3 · Accepted solution]

1) FirstSortedValue() might work for you here.

2) Assuming ValueDate is already a date, do you actually need the date() function? What is the actual value of ValueDate - is it 2010/08/31 ?

[#4]

Hi

I think that the expression 6, need a "TOTAL" in the function MAX and you need verify the "(" and ")" because you have a 4 "(" and 5 ")"

COUNT(IF(ValueDate = Date(Max(TOTAL ValueDate) ), BalanceRemaining))

Do you have a qvd example ?

[jonathandienst]

Steve

Thanks for your suggestion which got me on the right track. The answer was:

FirstSortedValue({<TaxStatus={'Cl'}>} Total <DealCode> BalanceRemaining, -ValueDate)

which works perfectly.

Jonathan