Max Date

[agsearle]

I have a table with customer ref, dates and amounts, but I only want the amount relating to the maximum date for each customer ref.

Cust Ref Date Amount
1 01/01/11 20
1 20/01/11 15
2 15/01/11 10

So from the above example I want to see cust 1 show a value of 15, being the amount next to the maximum date of that cust.

Something like: (although obviously this doesn't work)

IF(MAX(date), Amount)

[#2]

if([Date]=max(total <[Cust Ref]> [Date], [Amount])

By default, max is over all of the dimensions so technically, 20 is the max amount over the dimensions Customer Ref = 1 and Date 1/1/11. That being said, you have to tell QV which dimension you actually want the max of which in this case, is [Cust Ref].

[#3 · Accepted solution]

Maybe (not tested) create a straight table with

Dimension = Cust_Ref

Expression = FirstSortedValue( Amount , -Date) FirstSortedValue give you the amount for the first Date (So using -Date allows to catch the last Date).

JJ

[agsearle]

Spot on thanks!