topic Select most recent date in QlikView

Select most recent date

Fri, 20 Jan 2012 12:18:19 GMT

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

Select most recent date

swuehl — Fri, 20 Jan 2012 12:22:51 GMT

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

Select most recent date

Fri, 20 Jan 2012 12:47:31 GMT

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

Select most recent date

swuehl — Fri, 20 Jan 2012 12:50:37 GMT

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?

Select most recent date

Fri, 20 Jan 2012 12:55:32 GMT

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.

Select most recent date

swuehl — Fri, 20 Jan 2012 13:01:58 GMT

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)

Select most recent date

Fri, 20 Jan 2012 13:11:56 GMT

Thanks, Stefan!