topic Re: compare a dollar amount <> 0 in QlikView

compare a dollar amount <> 0

Fri, 12 Oct 2012 19:22:40 GMT

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.

Re: compare a dollar amount <> 0

swuehl — Fri, 12 Oct 2012 19:32:06 GMT

Try

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

Re: compare a dollar amount <> 0

johnw — Fri, 12 Oct 2012 19:32:07 GMT

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.

Re: compare a dollar amount <> 0

rwunderlich — Fri, 12 Oct 2012 19:36:03 GMT

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

-Rob

Re: compare a dollar amount <> 0

Fri, 12 Oct 2012 19:45:09 GMT

Thanks so much this one worked.

Lynda

Lynda Wales

800 845-3711 ext. 2366

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

Re: compare a dollar amount <> 0

rwunderlich — Sun, 14 Oct 2012 18:37:55 GMT

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

Re: compare a dollar amount <> 0

swuehl — Mon, 15 Oct 2012 13:23:36 GMT

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"

Re: compare a dollar amount <> 0

Mon, 15 Oct 2012 13:59:49 GMT

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>

Re: compare a dollar amount <> 0

swuehl — Mon, 15 Oct 2012 14:23:24 GMT

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

Re: compare a dollar amount <> 0

Mon, 15 Oct 2012 14:25:38 GMT

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>

Re: compare a dollar amount <> 0

johnw — Mon, 15 Oct 2012 17:26:11 GMT

Rob Wunderlich wrote:
@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.

You're right of course. They're not the same. I've gotten rusty.

I tend to use -= and *= frequently when doing set analysis. I believe that users expect the system to be responsive to their selections. So if we want to exclude 0, I would use -={0} so that if the user selects one or more of the remaining values, the chart will be restricted to what they select. Otherwise, we'll always show all values other than 0, which I think is typically not what users want or expect. It may be appropriate in some cases, of course.

The same applies to the use of *= when restricting to specific values. If you just use =, exactly those values and all of those values will always show, even if the user selects just one of them for more careful analysis. With the *=, their choices are restricted to the values listed, but they can still narrow the chart down by making selections. It's sometimes appropriate to use = for a list, but I think I more frequently use *=.