Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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.

Tags (4)
1 Solution

Accepted Solutions
MVP
MVP

Re: compare a dollar amount <> 0

Try

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

View solution in original post

10 Replies
MVP
MVP

Re: compare a dollar amount <> 0

Try

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

View solution in original post

MVP
MVP

Re: compare a dollar amount <> 0

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.

MVP & Luminary
MVP & Luminary

Re: compare a dollar amount <> 0

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

-Rob

Not applicable

Re: compare a dollar amount <> 0

Thanks so much this one worked.

Lynda

Lynda Wales

800 845-3711 ext. 2366

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

MVP & Luminary
MVP & Luminary

Re: compare a dollar amount <> 0

@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.

MVP
MVP

Re: compare a dollar amount <> 0

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

Re: compare a dollar amount <> 0

*

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>

MVP
MVP

Re: compare a dollar amount <> 0

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

Re: compare a dollar amount <> 0

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>