QlikView and set analysis syntax (indirect date search)

[bertrand_herard]

Hi,

I'm fighting with QlikView Syntax

I want to select a date interval with two variables (date min / date max) (this point is ok)

Then in a table, I want to get the status corresponding to the max available date in the date intervals (between two variables)

I think it was easy (at least not really hard )

But I'm fighting with the Set Analysis syntax

Can you please help me ?

(I've attached a sample example)

Best regards

[luciancotea]

Max date by Key:

=max({< DateNum={">=$(=Date(vMinDateCal))<=$(=Date(vMaxDateCal))"} >} DateNum)

For the second expression, you have two statuses for the same day.

[rubenmarin]

The 2nd xpression can be:

FirstSortedValue({<DateNum={">=$(=Date(vMinDateCal))<=$(=Date(vMaxDateCal))"}>} Status, -DateNum)

But (as said by Lucian) there are different values for the same day and key, so it will return null() in that conditions.

[bertrand_herard]

Thanks Lucian and Ruben,

that's a good start :

I forgot an important point :  If two different status are available, take the one with the biggest field Flag value

(I've edited the qvw with a new "Flag" field").

Best regards

[rubenmarin]

Hi Bertrand, you can try with:

Aggr(If(DateNum=Max(TOTAL <KEY> {<DateNum={">=$(=Date(vMinDateCal))<=$(=Date(vMaxDateCal))"}>} DateNum) and Flag=Max(TOTAL <KEY, DateNum> Flag), Status), KEY, DateNum, Flag)