How to create a Not Equal to Zero condition in Set Analysis?

[john_henry]

I know how to do set analysis with text, but with numerical values, I'm not sure.   I have a condition where I need to exclude all $0 values from my calculation.  Here is my syntax so far:

[Gross Sales Order US Dollar Amount]-=0

How do I select where values are not equal to zero?

[thkarner]

Maybe I´m blind, but I only could find the syntax for -= here

http://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-modi...

I wasn´t aware of the =- syntax and also cound´t find it in any of my books but also not at the help.qlik.com.

Do you know where this is documented?

R, Thomas

[thkarner]

I tested it based on Rob´s reply.

In case you always don´t want to select the values with 0 you need.

<[Gross Sales Order US Dollar Amount]=-{0}>

Detailed Explanation:

This set analysis expression selects ALL values in the field Gross Sales Order US Dollar Amount except the value 0.

It means also to disregard a selection in this field (which is for sure unlikely but could happen). Even with a selection on this field the result stays correct.

In comparision, if you would use

<[Gross Sales Order US Dollar Amount]-={0}> it would take all current possible values and excludes the value 0.

This means, in case the user makes a selection on the field Gross Sales Order US Dollar Amount, the system would take this selection and remove the 0 value from the current selection, which leads to a different result.

Rob, thanks for this (although I couldn´t find the documentation for this). But the day is saved, I learned something new.

[rwunderlich]

Isn't that the same as "not zero"?

-Rob

[rwunderlich]

I'll admit the doc is a bit quiet on this. To clarify with a wonky explanation:

"-="  (note the lack of a space) is an assignment operator. It's a single operator.

"= -" (note the space, optional) are two separate operators. "=" is the assignment operator. "-" is a unary operator that means "negate what follows".

Some examples for the zero question:

<Sales = {0}> // Select Sales equal to 0

<Sales -= {0}> // Remove 0 from the current selections

<Sales = - {0}> // Select Sales that are not 0

<Sales = {">0"}> // Select Sales GT 0

<Sales = {"<0"}> // Select Sales LT 0

And since it's Friday, here's one more using an advanced search:

<Sales = {"=Sales<>0"}>

A Friday question for everyone.  This last set would behave the same as which set in the examples above?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[rohitraut]

Hello john,

Try this ,

Script level,

Laod

[Gross Sales Order US Dollar Amount]

From ABC where [Gross Sales Order US Dollar Amount] <> 0;

Or

Expression

If(sum([Gross Sales Order US Dollar Amount] ) > 0 ,sum([Gross Sales Order US Dollar Amount] )

[Anonymous]

‌tthe second expression doesn't do the trick. Greater than zero is not the same as not equal to zero. It discards negative values

[rohitraut]

Try this,

If(sum([Gross Sales Order US Dollar Amount] ) > 0 or  sum([Gross Sales Order US Dollar Amount] ) < 0 ,sum([Gross Sales Order US Dollar Amount] )

[rubenmarin]

There is also another options to ignore selections in Sales field and remove zeros that keeps syntax checker enabled:

<Sales={"<>0"}>

Or using E() function:

<Sales=E({<Sales={0}>} Sales)>

But if syntax checker is not a need I would also go with =-

And -= in case users can make selections on Sales field (maybe filtering movements less than X$?)

[john_henry]

I was just curious what the syntax looked like if I had a less than scenario or greater than scenario.

[rwunderlich]

Ruben,

My experience is that

<Sales={"<>0"}>

does not work as expected.  You would think that "<>" would be interpreted as Not Equal, but what I see is the interpreter treats this as two separate operators

<  (LT nothing)

>0 (GT 0)

It appears to work if you have only positive numbers, but it would exclude negative numbers if present.

What does work is:

<Sales = {"<0>0"}>

I've attached my test qvw

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

.