Select most recent date

[#1]

Hi,

Does anybody know how to select the most recent date from a table? I have a table that looks like this:

Customer	Status	StatusDate
A	1	02.01.2012
A	2	05.01.2012
A	3	10.01.2012
B	3	07.01.2012
B	4	12.01.2012
C	1	05.01.2012
C	2	18.01.2012

I want to select the status with the most recent date for each customer and store the result into a new table. The result will look like this:

Customer	Status
A	3
B	4
C	2

Regards,
Tore

[swuehl]

In your chart with dimension customer, try

=FirstSortedValue( Status, -StatusDate)

I assume your StatusDate is of Date type with a numerical representation and that you don't have multiple Status for any given date per customer.

Hope this helps,

Stefan

[#3]

It works, thanks Stefan.

I also need to count the number of customers for each status. Is this possible without creating a new table?

Regards,
Tore

[swuehl]

The number of customers for each status, only for the most recent status per customer? So the result would be 1 for each line in above table?

[#5]

That’s right, the number of customers for each status, only for the most recent status per customer. In the example above, the result would be 1 for each status, but my table has more customers.

The calculation can be done in a different chart.

[swuehl · Accepted solution]

You could use a calculated dimension in your chart to classify your Customer by recent Status:

=aggr( FirstSortedValue(Status,- StatusDate), Customer)

Then use as expression:

=count(distinct Customer)

[#7]

Thanks, Stefan!