Recently on QlikCommunity, I have proposed a solution however the very brilliant Oleg Troyansky proposed an even better solution: http://community.qlikview.com/forums/p/25381/96915.aspx#96915
Hi answer to the problem (excluding nulls, zero length strings, etc.) was this:
sum({$ < TextField = {"=len(trim(TextField))>0"}>} Qty )
Brilliantly fantastic! Of course, it is even more brilliant if you understand it and, alas, I just couldn't fathom it. It works. It works great. But I didn't see it.
Then my genius colleague, Karol Dorniak, spelt it out for me :- anything that you can type into a search dialog in QlikView can be used in a set! And that includes any "advanced" search syntax!!!
So this is what Oleg was doing - using advanced search. Brilliant.
When you right-click on a List Box in QlikView, there are 3 search options available - Search, Fuzzy Search and Advanced Search. When you click on a list box and just start typing, you get the default search for that List Box (usually the normal search but you can set it to Fuzzy as default on the General tab of the List Box properties). Did you know that you can delete the "**" from the default search and change it to a "~" to start doing a fuzzy search. You can also start typing an "=" followed by an expression (e.g. "=Sum(SalesAmount) > 5000") to do an advanced search - you don't need to go through the advanced search dialog.
So, you can use any of the 3 standard searches by just replacing the search condition with something else ("**", ">", "><", ">=", "~", "=...") Anything that you can type into a search can be used inside a Set!
Man, does this QlikView rock!
