Trying to Filter an Expression and Having No Luck

[lnoble3023]

Hi - 

I'm trying to filter a couple expressions by Num(Count) and Num(Sum).  Both are kind of similar, so I'll pose the question on the Num(Count) example. 

(Ex 1)  Currently my formula is:  num(Count({<[Total Outstanding]={'>0'},[Off Aging]={'no'},[C&C Group]={'Direct'},[GL Country]={'BZL'},[Transaction Type]={'Direct Electronic Invoice','Direct IC Credit Card Invoice'}>} [Bill/Invoice #]))  .  This seems to work ok...

But when I try to add in one more filter to the formula to filter a field called "Orig - Total O/S", it makes all data disappear.  I'm basically trying to add one more filter to show only records where "Original Amount" - "Total Outstanding" equal zero

(Ex 2)  This new formula shows as:  num(Count({<[Total Outstanding]={'>0'},[Off Aging]={'no'},[C&C Group]={'Direct'},[GL Country]={'CA'},[Transaction Type]={'Direct Electronic Invoice','Direct IC Credit Card Invoice'},[Orig - Total O/S]={'0'}>} [Bill/Invoice #]))

It seems my 'new' formula doesn't like how I wrote the bolded syntax I guess, as in adding it...it makes all my data disappear.  The definition for field [Orig - Total O/S] is:  money([Original Amount]-[Total Outstanding], '$#,##0.00;($#,##0.00)') as [Orig - Total O/S]

Attached is my data.  The data that shows appears when I use the formula for Example 1.  When I use the Example 2 formula, I get 'no data' at all.

Any help in making my expression work would be greatly appreciated.  

thank you,

Lee

[jwjackso]

In the sample data that you provided, your 2nd formula works for [GL Country]='BZL', there is no data for [GL Country]='CA'.

[jwjackso]

I made one change to the formula, I changed [Total Outstanding]={'>0'} to [Total Oustanding]={">0"}

[lnoble3023]

thanks for the reply.  My mistake in copying the formula.  The Ex 2 should be the same as the Ex 1 except the red text.  It should read:    num(Count({<[Total Outstanding]={'>0'},[Off Aging]={'no'},[C&C Group]={'Direct'},[GL Country]={'BZL'},[Transaction Type]={'Direct Electronic Invoice','Direct IC Credit Card Invoice'},[Orig - Total O/S]={'0'}>} [Bill/Invoice #]))

Were you able to get data using BZL?  I can't get data to show

[jwjackso]

The formula worked after changing to double-quotes

[Total Outstanding]={">0"}

[lnoble3023]

thanks, Jwjackso.  I tried changing, like you said, to show 

         num(Count({<[Total Outstanding]={">0"},[Off Aging]={'no'},[C&C Group]={'Direct'},[GL Country]={'BZL'},[Transaction Type]={'Direct Electronic Invoice','Direct IC Credit Card Invoice'},[Orig - Total O/S]={'0'}>} [Bill/Invoice #]))

this works ok for me, yes, until I again add in the last portion of: [Orig - Total O/S]={'0'}  , and then I get no data again.  Are you getting data to show with that portion of the script added, as I am not.  I even tried with the {"0"}, and still nothing.  I really appreciate the help

[jwjackso]

This is the result that I get.

[lnoble3023]

Thanks, Jwjackso.  Yes, that is correct - 99.  Hmmm, that's so strange that I don't get that when I use the same expression.  I'm wondering if it has something to do with how i have the field [Orig - Total O/S] defined.  For all my other fields, I have them defined in my 'extract' layer.  Only this field I have defined in my 'presentation' layer - under the 'Main' script.  I'm far from an advanced Qlikview user...I'm excited my expression works right, but confused why output isn't showing...  Thanks