topic To exclude a value from application in QlikView

To exclude a value from application

Anonymous — Mon, 04 Mar 2013 11:33:10 GMT

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

Re: To exclude a value from application

Gysbert_Wassenaar — Mon, 04 Mar 2013 11:38:03 GMT

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.

Re: To exclude a value from application

Mon, 04 Mar 2013 11:55:14 GMT

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

Re: To exclude a value from application

Anonymous — Tue, 05 Mar 2013 07:20:27 GMT

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

Re: To exclude a value from application

Anonymous — Tue, 05 Mar 2013 12:51:58 GMT

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

Re: To exclude a value from application

Anonymous — Wed, 27 Mar 2013 11:49:21 GMT

Hi Shaik,

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

Thanks,

Leni

Re: To exclude a value from application

Thu, 28 Mar 2013 12:44:37 GMT

Leni,

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