topic Re: Set Analysis : 2 conditions on same field in QlikView

Set Analysis : 2 conditions on same field

emmanueld — Tue, 22 Mar 2016 15:03:43 GMT

Hello everyone,

I have in my Qlikview a few set analyses having 2 conditions on the same field, like for instance "Date > X AND Date < Y", and I am wondering what is the difference between the two following syntaxes:

Date = {">=$(v_Today)"}*{"<=$(v_variable)"}

Date = {">=$(v_Today) <=$(v_variable)"}

After running a few tests, I feel like the second syntax sometimes works like an "AND" and sometimes like an "OR", why is that?

Actual case :

This seems to work (Formatted date is a YYYYMMDD-formatted string, just as the two variables, calculated in the script):

FormattedDate = {">=$(v_DateToday) <=$(v_DateSixMonths)"}

But this doesn't, it behaves like a "OR":

FormattedMonth = {">=$(=min(START_DATE)) <=$(=max(END_DATE))"}>}

and I had to correct it by

FormattedMonth = {">=$(=min(START_DATE))"} * {"<=$(=max(END_DATE))"}>}

(Formatted month is a YYYYMM-formatted string, and START_DATE and END_DATE are two YYYYMM-formatted ID fields, impacted by the selection of the equivalent labels in a Multi box)

Thanks

Re: Set Analysis : 2 conditions on same field

Gysbert_Wassenaar — Tue, 22 Mar 2016 15:39:51 GMT

I am wondering what is the difference between the two following syntaxes:

Date = {">=$(v_Today)"}*{"<=$(v_variable)"}

Date = {">=$(v_Today) <=$(v_variable)"}

They're functionally the same. The intersection of two sets in the first is an AND and so is the second syntax.

But this doesn't, it behaves like a "OR":

FormattedMonth = {">=$(=min(START_DATE)) <=$(=max(END_DATE))"}>}

It's not an OR, it simply does not work at all and thus creates a set of all records. Why it doesn't work? Probably because the date formats of FormattedMonth, START_DATE and END_DATE differ. I consider this behavior a bug personally.

Re: Set Analysis : 2 conditions on same field

emmanueld — Wed, 23 Mar 2016 08:12:18 GMT

Yes you must be right about it simply not working at all I'm surprised though if it's a formatting issue, because it works with the * in between. Thank you Gysbert for your clarification.

Re: Set Analysis : 2 conditions on same field

Gysbert_Wassenaar — Wed, 23 Mar 2016 09:31:24 GMT

Yes, that's why I consider it a bug. Comparisons using >, >=, < and <= should always use the numeric values. Formatting of the dual value shouldn't make a difference imho.