topic Re: Set Analysis - exclude range in QlikView

Set Analysis - exclude range

dafnis14 — Thu, 22 May 2014 10:20:13 GMT

Hi,

Any ideas about how to write the expression so that it will include the values not in the the current defined dates range?

I'm working in version 10, if it matters.

SuppReturns.ConfDate={">=$(vMinReturnDate)<=$(vMaxReturnDate)"}

Thanks!

Re: Set Analysis - exclude range

Thu, 22 May 2014 10:26:32 GMT

Try

SuppReturns.ConfDate-={">=$(vMinReturnDate)<=$(vMaxReturnDate)"}

Re: Set Analysis - exclude range

ashfaq_haseeb — Thu, 22 May 2014 10:28:15 GMT

if you are trying to calculate everything except current selection then Use

sum({1 - $}{<your expresion>} Sales)

It will return you inverse of current selection.

Attached document might help you

Regards

ASHFAQ

Re: Set Analysis - exclude range

ashwanin — Thu, 22 May 2014 10:28:48 GMT

Try -= option

Re: Set Analysis - exclude range

dafnis14 — Thu, 22 May 2014 12:17:04 GMT

Hi,

When i tried it, it gave an indication of a syntax error (red line).

I guess i can ignore it...

Re: Set Analysis - exclude range

tresB — Thu, 22 May 2014 12:19:39 GMT

Yes, you can ignore it. That's a qv parser error, otherwise syntax works fine.

Re: Set Analysis - exclude range

Thu, 22 May 2014 14:21:05 GMT

I like to avoid the proposed -= because it causes those nasty red lines and removes the benefits of syntax checker for the rest of the (sometimes long) expression.

You can combine sets in set analysis. One set is the one you define between {} after the field name. In this case we could do the union of sets {"<=$(vMinReturnDate)"} and {">=$(vMaxReturnDate)"}by using the +, so like this:

SuppReturns.ConfDate = {"<=$(vMinReturnDate)"} + {">=$(vMaxReturnDate)"}.

To get the range between, you actually used syntax I wasn't familiar with (but can be found from the help as well, it seems). I would have used * to get the intersection of the sets, like this:

SuppReturns.ConfDate = {">=$(vMinReturnDate)"} * {"<=$(vMaxReturnDate)"}

I hope these help!

Best regards,

Henry

Re: Set Analysis - exclude range

dafnis14 — Sat, 24 May 2014 06:02:16 GMT

Hi Henry,

Thanks for the very bright explanation.

I definitely agree with your recommendation.

Dafnis