Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try
count({<Year= {"$(=Max(Year))"} ,[Total Line Amount] = {">0<0"}, [Line Item Type] = {'I'}>} [HSDETITEMNUMBER])
Try
count({<Year= {"$(=Max(Year))"} ,[Total Line Amount] = {">0<0"}, [Line Item Type] = {'I'}>} [HSDETITEMNUMBER])
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.
[Total Line Amount] = ("<>.00")
-Rob
Thanks so much this one worked.
Lynda
Lynda Wales
800 845-3711 ext. 2366
lyndawales@unitedsportingco.com<mailto:lyndawales@unitedsportingco.com>
@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.
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"
*
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>
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
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>