Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

compare a dollar amount <> 0

The expression box isn't something that I have mastered yet.

I need to count all the lines unless the Total Line Amount is zero, which means we didn't ship anything.

count({<Year= {"$(=Max(Year))"} , [Line Item Type] = {'I'}>} [HSDETITEMNUMBER])  THIS WORKS FINE but include the lines where we didn't ship anything.

I need to add Total Line Amount <> .00

count({<Year= {"$(=Max(Year))"} ,[Total Line Amount] =  (<.00/>.00) [Line Item Type] = {'I'}>} [HSDETITEMNUMBER])    This didn't work.

Can anybody give me some help? I know this is a elementary problem.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

count({<Year= {"$(=Max(Year))"} ,[Total Line Amount] = {">0<0"}, [Line Item Type] = {'I'}>} [HSDETITEMNUMBER])  

View solution in original post

10 Replies
swuehl
MVP
MVP

Try

count({<Year= {"$(=Max(Year))"} ,[Total Line Amount] = {">0<0"}, [Line Item Type] = {'I'}>} [HSDETITEMNUMBER])  

johnw
Champion III
Champion III

I'm a little unclear, but probably this:

count({<Year= {"$(=Max(Year))"} , [Total Line Amount]-={0}, [Line Item Type] = {'I'}>} [HSDETITEMNUMBER])

Edit: To clarify, swuehl's version says "search for and include line amounts that are either greater than zero or less than zero."  Mine says "exclude line amounts that are zero".  Both should return the same thing.  I prefer to avoid search expressions in favor of literals where possible, but I can think of no practical reason for doing so.  It's more of an aesthetics thing, which is of course personal.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

[Total Line Amount] = ("<>.00")

-Rob

Not applicable
Author

Thanks so much this one worked.

Lynda

Lynda Wales

800 845-3711 ext. 2366

lyndawales@unitedsportingco.com<mailto:lyndawales@unitedsportingco.com>

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

@John,

Both methods would return the same thing only if there were no selections in [Total Line Amount].

The "=" modifier sets the selections to a specific value.

The "-=" modifies current selections in that field.

-Rob.

swuehl
MVP
MVP

John Witherspoon wrote:


Edit: To clarify, swuehl's version says "search for and include line amounts that are either greater than zero or less than zero." 

IMHO, it's interesting to know that QV's interpretation of a search expression like ">FirstLimit<SecondLimit"

is depending on the comparison of FirstLimit and SecondLimit itself.

If FirstLimit is smaller than SecondLimit, the resulting data set from the search will be the intersection of the two data sets resulting from the two comparisons with field values, where the resulting data set will be the union of both, if FirstLimit is larger than SecondLimit.

A simple example: A field with a list of values ranging from one to 10.

">5<8"

will return 6, 7.

While ">8<5" will return 1, 2, 3, 4, 9, 10 (where you would expect an empty set if the same logic as above would apply).

So QV is applying some of its artificial intelligence to determine the logical operator.

Though the result might be what one implicitely would expect, I haven't found any documentation about this anywhere (like for most of the search capabilities, one of the worst documented parts in QV documention, IMO).

And one needs to consider this if the search limits are not literals, but dynamically derived from other fields etc.

(For example when you expect always StartTime < EndTime, but when your data proves something else...)

Regards,

Stefan

P.S: I would appreciate if QV would accept a search string like "<>.00",
but it just seems to interprete this as "<{MissingValueForLimit}>.00", i.e. ">.00"

Not applicable
Author

*

count({<Year= {"$(=Max(Year))"} ,[Total Line Amount] = {">0<0"}, = {'I'}>} )

I tried this one and it seemed to work.

Is it working because the value is comparing against zero?

Would it for example count a 1 for -5.00 and then count a 1 for 10.00 ?

Thanks

Lynda

Lynda Wales

800 845-3711 ext. 2366

lyndawales@unitedsportingco.com<mailto:lyndawales@unitedsportingco.com>

swuehl
MVP
MVP

Hi Lynda,

I am not sure if I got your question correctly, but I believe the logic in the set modifier is a union of both sets: one set of values for Total Line Amount values where values are >0 and one set for values <0.

Just like John said: "search for and include line amounts that are either greater than zero or less than zero."

So I think this is what you requested, right?

Sorry if my previous post was maybe more confusing then of any help.

Stefan

Not applicable
Author

Stefan,

Yes… That is right.

I had checked it and thought that it work.

I so appreciate the help.

Lynda

Lynda Wales

800 845-3711 ext. 2366

lyndawales@unitedsportingco.com<mailto:lyndawales@unitedsportingco.com>