Retrieve a value based on two dates comparison using Set Analysis in a table

Fri, 26 Sep 2014 18:53:58 GMT

Hi guys,

Im stuck with this issue for over a month, as my questions says I want to retrieve a value based on two dates comparison using Set Analysis in a table, in detail, I want to get a value [Avance Esperado] from the Max([Fecha de Avance])

If I use the expression

=Max({$<[Fecha de Avance] = {"$(=Max([Fecha de Avance]))"}>} [Avance Esperado])

The row "Avance esperado" shows value only where the date matches with the maximum date in the whole document, not per [Proyecto], as you can see in the image below:

If I use instead this expression:

=Max({$<[Fecha de Avance] += {"$(=Max([Fecha de Avance]))"}>} [Avance Esperado])

The result seams to be right:

But if you check on the source document which is excel, you realize it is picking the maximum value for [Proyecto] but no with the matching maximum date:

So the value picked in [Avance Esperado] should be 87.50% as marked with blue, due the date 17/09/2014 is the maximum date for Janssen - Kronos project, instead it is picking 100% as marked in red.

I am attaching the qvw file, and the xlsx source file.

Can you help me to solve this?

Thank you very much.

topic Retrieve a value based on two dates comparison using Set Analysis in a table in QlikView

Retrieve a value based on two dates comparison using Set Analysis in a table