topic Max Date in QlikView

Max Date

agsearle — Thu, 24 Feb 2011 17:43:09 GMT

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)

Max Date

Thu, 24 Feb 2011 17:57:35 GMT

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].

Max Date

Thu, 24 Feb 2011 21:23:08 GMT

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).

Max Date

agsearle — Fri, 25 Feb 2011 11:58:48 GMT

Spot on thanks!