topic Re: QlikView and set analysis syntax (indirect date search) in QlikView

QlikView and set analysis syntax (indirect date search)

bertrand_herard — Mon, 14 Mar 2016 16:30:46 GMT

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

Re: QlikView and set analysis syntax (indirect date search)

luciancotea — Mon, 14 Mar 2016 17:27:34 GMT

Max date by Key:

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

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

Re: QlikView and set analysis syntax (indirect date search)

rubenmarin — Mon, 14 Mar 2016 17:45:00 GMT

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.

Re: QlikView and set analysis syntax (indirect date search)

bertrand_herard — Mon, 14 Mar 2016 21:25:49 GMT

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

Re: QlikView and set analysis syntax (indirect date search)

rubenmarin — Mon, 14 Mar 2016 22:23:39 GMT

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)