Hi Garry,
In the case of a calculated filter field, Jet doesn't know what type of results the calculated filter field will return so it doesn't know how to set the filter. The results you're seeing are probably due to the filter being treated like text instead of a number. In this case, you should be able to fix it by prefixing your filter by a value that tells Jet the type of the calculated filter field like this:=NL("Rows","Customer",,"+=NF(,""Sales (LCY)"",""Date Filter"",$G$7)-NF(,""Sales (LCY)"",""Date Filter"",$H$7)","NUMBER&<-2000")
Does that work for you?
Regards,
Hughes
Date
Votes
5 comments
-
Jet Reports Historic Posts Hi,
I think I am missing something really obvious, but I can't see what.
Basically I am comparing this month's invoice value with the previous month. The following NL function works perfectly to sort the results by the difference:=NL("Rows","Customer",,"+=NF(,""Sales (LCY)"",""Date Filter"",$G$7)-NF(,""Sales (LCY)"",""Date Filter"",$H$7)","*")
However when I then try to filter the results (for example only to show accounts where the sales are more than 2000 lower) it just doesn't work. What I was trying was this:=NL("Rows","Customer",,"+=NF(,""Sales (LCY)"",""Date Filter"",$G$7)-NF(,""Sales (LCY)"",""Date Filter"",$H$7)","<-2000")
Can anyone see what is wrong with the filter on the second example????
Many thanks.
Regards,
Garry. -
Jet Reports Historic Posts Hi Garry!
Can you tell us what value you get as error?
If your cell is just blank, then there are no rows to display. (that's my result on your second formula)
Regards,
Bert -
Jet Reports Historic Posts Hi Bert,
I am getting results, but they don't relate to the filter value.
If I run the query for everything (Filter: "*") then I get values ranging from -14,000 to plus 15,000. There are 33 values between -2000 and -14000.
If I run it with a filter of "<-2000" I am getting 65 results. One is -14,000 and one is -12,000 but the rest range from -1889 to -1 (all outside the filter range).
I tried another filter of ">2000" and I got values ranging from 21 to 9,000 (again may lower than the filter value).
I just can't understand how the results I am getting relate tot eh filter value. There doesn't seem to be any link at all that I can see.
Regards,
Garry. -
Jet Reports Historic Posts Hi Hughes,
That solved it. Many thanks.
Apart from "NUMBER", are their any other prefixes which might be helpful in the future to let Jet know the type of filter which is being used? I can't find this anywhere in the help file.
Many thanks.
Regards,
Garry. -
Jet Reports Historic Posts Garry,
The other prefix you can use is DATE& for when the result returned by the calculated filter field is a date value. Calculated filters and fields are not in the Jet documentation. They are generally considered too hard for most people to create themselves, even with documentation to help them.
Regards,
Hughes
Please sign in to leave a comment.