Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

To exclude a value from application

Hi all,

I have a requirement like below:

Its for an example:

Field1 Field2 Field3

WW    1231  aaa

XXX      3654  bbb

YYY    4576  ccc

ZZZ     5735   ddd

Here I want to exclude the value XXX from my report.

My report is created in pivot table with Field1 & 3 as dimensions and Field2 will me the expression like sum(field2)

I tried to exclude the value by not selecting XXX from listbox and checked but it not showing the correct value. As in my report I have a row with - (hyphen) for Field1 dimension when I am adding it to pivot table.

So while I am making selection like above the null value also got disappeared so it is not getting the relevant value.

I want to get the value like totalAmount - totalAmount of XXX value.

Thanks in Advance.

~Leni Balakrishnan

6 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Exclude the value in the expression: sum({<Field1 -= {'XXX'} >} Field2). The editor doesn't understand -=, but that's a bug. It is the correct syntax.

Another options is to use a calculated dimension instead of Field1: =if(Field1<>'XXX', Field1) and check the option Suppress When Value Is Null for the calculated dimension.

edit:

You really should replace the nulls with real values in the script. But if necessary you can do it in a calculated dimension too:

=if(Field1<>'XXX', if(len(trim(Field1))=0,'-',Field1)). Then check Suppress When Value Is Null for the calculated dimension.



talk is cheap, supply exceeds demand
Not applicable
Author

for field Field1 enter caculated column like =if( Field1<>'XXX',Field1)

Anonymous
Not applicable
Author

Hi Gysbert,

Thanks for your solution

But its excludes the values of null too. As I told above I my report I have one row with null in dimension.

Thanks,

Leni Balakrishnan

Anonymous
Not applicable
Author

Hi balakrishnan,

     if you getting  - (hyphen) in data ,you can exclude that one also in setanalysis,

         sum({<Field1 -= {'XXX','-'} >} field2).

i think above one is use full for you,it will be exlude XXX and -(hyphen).

Regatds

SHAIK

Anonymous
Not applicable
Author

Hi Shaik,

But I need to include that. I need to include '-' value and exclude XXX value alone

Thanks,

Leni

Not applicable
Author

Leni,

As Gysbert said change the null values in the script and apply set analysis on the field1.