Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Maybe I´m blind, but I only could find the syntax for -= here
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
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.
Isn't that the same as "not zero"?
-Rob
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
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] )
tthe second expression doesn't do the trick. Greater than zero is not the same as not equal to zero. It discards negative values
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] )
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$?)
I was just curious what the syntax looked like if I had a less than scenario or greater than scenario.
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
.