topic Getting the most recent transaction in QlikView

Getting the most recent transaction

jonathandienst — Tue, 31 Aug 2010 16:01:03 GMT

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.

Max(ValueDate) - works as expected giving the date for the most recent transaction for the deal 2010/08/31
MonthStart(Max(ValueDate)) - works as expected giving 2010/08/01
MonthStart(Column(1)) - works as expected giving 2010/08/01
Count(If(ValueDate =Date( '2010/08/31'), BalanceRemaining)) - works as expected giving 1 (never more than 1 per day)
Count(If(ValueDate = Date(Column(1)), BalanceRemaining)) - does NOT work (returns 0, should be 1)
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:

What is the correct expression to get the BalanceRemaining for the last ValueDate for a DealCode (which is the table dimension)?
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

Getting the most recent transaction

jonathandienst — Wed, 01 Sep 2010 13:32:59 GMT

(Bump) Anyone?

Getting the most recent transaction

Wed, 01 Sep 2010 13:54:31 GMT

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 ?

Getting the most recent transaction

Wed, 01 Sep 2010 14:02:50 GMT

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 ?

Getting the most recent transaction

jonathandienst — Wed, 01 Sep 2010 14:14:55 GMT

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