topic Re: Trying to Filter an Expression and Having No Luck in QlikView

Trying to Filter an Expression and Having No Luck

lnoble3023 — Mon, 26 Apr 2021 20:10:23 GMT

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

Re: Trying to Filter an Expression and Having No Luck

jwjackso — Mon, 26 Apr 2021 21:21:28 GMT

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

Re: Trying to Filter an Expression and Having No Luck

jwjackso — Mon, 26 Apr 2021 21:28:11 GMT

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

Re: Trying to Filter an Expression and Having No Luck

lnoble3023 — Mon, 26 Apr 2021 21:30:45 GMT

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

Re: Trying to Filter an Expression and Having No Luck

jwjackso — Mon, 26 Apr 2021 22:03:53 GMT

The formula worked after changing to double-quotes

[Total Outstanding]={">0"}

Re: Trying to Filter an Expression and Having No Luck

lnoble3023 — Tue, 27 Apr 2021 01:25:32 GMT

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

Re: Trying to Filter an Expression and Having No Luck

jwjackso — Tue, 27 Apr 2021 13:04:15 GMT

This is the result that I get.

Re: Trying to Filter an Expression and Having No Luck

lnoble3023 — Tue, 27 Apr 2021 16:29:19 GMT

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