topic the most recent date in QlikView

the most recent date

Fri, 08 Oct 2010 19:50:41 GMT

I have: 'name', 'value' and 'date'

I want to appear only the product with the most recent date, provided that this value is not null

example:

A___565___01/03/09

A___null___19/11/09

A___120___30/02/10

A___null___21/05/10

A___null___06/08/10

in this case would be:

A___120___30/02/10

because the value of this product is different from 'null' and because it is the most recent

can someone help me?

the most recent date

Fri, 08 Oct 2010 20:26:23 GMT

I think that are a single row "A___120___30/02/10"

You need to use a string function to separete in three fields how you say.

In this case:

NAME: Left('A___120___30/02/10', 1)

VALUE: Right(Left('A___120___30/02/10', 7), 3)

DATE: Right('A___120___30/02/10', 😎

After you can use the IF statement to show your result.

the most recent date

pover — Fri, 08 Oct 2010 20:46:42 GMT

There should be a better way, but try a formula like this:

sum({$<value -= {}>} if(date=aggr(nodistinct max(date),name),value))

It doesn't have to be a sum and the set analysis "value -= {}" is to get rid of the null values.

Edit: This formula considers that you have more than one product name and that you want to see the latest date for each product name in a table with the dimensions product name and date.

Regards.

the most recent date

Fri, 08 Oct 2010 21:10:22 GMT

See this example.

the most recent date

Fri, 08 Oct 2010 21:27:42 GMT

did not work

the most recent date

pover — Fri, 08 Oct 2010 22:07:16 GMT

Ok, but am I correct that you want a table that shows the value from the lastest date of various products or is Eduardo's solution correct?

the most recent date

Wed, 13 Oct 2010 18:28:43 GMT

I really want it what you said, but he can not get a date whose value is null ...

Eduardo think that the "A___200___12/03/10" was just a field ... but I used "___" to separate the fields... I have 3 columns and not 1 field.

Please do not send me the file. qvw .... Send me examples

the most recent date

Wed, 13 Oct 2010 19:24:50 GMT

DATE(MAX(TOTAL IF(Value <> '', Date)), 'DD/MM/YY')

the most recent date

Wed, 13 Oct 2010 19:34:36 GMT

did not work out too... but I used:

if(VALUE>=0,maxstring(if(VALUE>=0,DATE)))

and it worked.