20 Replies Latest reply: Sep 11, 2017 5:33 PM by Ruben Marin

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

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?

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

[Gross Sales Order US Dollar Amount ] = -0

Note the position of the "-", to the right of "=".

-Rob

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

I don't see a change when I move the "-" sign to the right of "="

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

Can you post the expression you are using?

-Rob

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

What about less than 0 or greater than zero?

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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

-Rob

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

Shouldn´t it be

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

?

Regards, Thomas

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

Or you use the if function instead of set analysis ...

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

-=  and =- are two different things in Set analysis.

-=0 means "remove 0 from the current selections".

=- means "select not 0".

-Rob

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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-modifiers-using-assignments-implicit-set-operators.htm

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

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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://qlikviewcookbook.com

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

in this case i will use the if function

if [Gross Sales Order US Dollar Amount]  <> 0, Parameter

You can use any of your dimensions for the parameter

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

Use <[Gross Sales Order US Dollar Amount] -= {0}> if it's numerical value or

If it's a string use <[Gross Sales Order US Dollar Amount] -= {'\$0'}> in your set analysis expression.

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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.

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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\$?)

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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://qlikviewcookbook.com

.

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

Ohh, I forgot to test negative values, thanks for clarification Rob.

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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] )

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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

• ###### Re: How to create a Not Equal to Zero condition in Set Analysis?

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] )