9 Replies Latest reply: Jun 22, 2016 11:38 AM by Sunny Talwar

# Exclude multiple values from Dimension

Hey Qlikers,

I have a customer table from which i am trying to exclude some internal customers. I would like to return zero and supress so they are not included in my ranking

here is my ranking

aggr(Rank(sum({\$<[Inv Month]={Apr}>}[Invoive Total]),1,1),[Customr Name])

and here is where i would like to exclude values from the dimension

=if( not Match ([Customr Name] = 'Cust1'  , 'Cust2, 'Cust3', 'Cust4 , 'cust5'), [Customr Name])

I am able to achieve this for one customer using this:

=If(not Match([Customr Name], 'cust1'), [Customr Name])

but no success when adding multiple exclusions.

Thank you

• ###### Re: Exclude multiple values from Dimension

Use this as Calculated Dimension

Aggr(If(Rank(SUM({<[Inv Month] = {'Apr'}, [Customer Name] = {'Cust1','Cust2','Cust3','Cust4','Cust5'}>}[Invoive Total]),4)<=5,[Customer Name]),[Customer Name])

Suppress When Value is null

Expression

SUM([Invoice Total])

• ###### Re: Exclude multiple values from Dimension

Thank you,

Please advise the best way to exclude multiple customers  (cust1,cust2 were examples...they will be case sensitive) so i can use this as the main customer list.

I will work the ranking expression once the customer list is sorted.

In short, we have internal companies in the list i wish to exclude.

How would you modify the below please?

=if( not Match ([Customr Name] = 'cus1'  , 'cus2, 'cus3', '), [Customr Name])

kind regards,

Dan

• ###### Re: Exclude multiple values from Dimension

If( Not Match([Customr Name] ,'cus1' , 'cus2, 'cus3'), [Customr Name])

• ###### Re: Exclude multiple values from Dimension

wow,

Thank you. this works a treat.

I have one more issue....these now show - but when i suppress zero values i still see them in the list as -

i need them excluded so they do not get assigned a rank.

Thank you

• ###### Re: Exclude multiple values from Dimension

Have you considered using set analysis instead of calculated dimensions?

• ###### Re: Exclude multiple values from Dimension

I will consider if you give some guidance.

I am open to suggestions

Thank you

• ###### Re: Exclude multiple values from Dimension

Create a new straight table or bar chart or whatever chart you have and use these things:

Dimension

[Customr Name]

Expression

Rank(Sum({\$<[Inv Month]={Apr}, [Customr Name -= {'Cust1'}>}[Invoive Total]),1,1)

for multiple exclusions

Rank(Sum({\$<[Inv Month]={Apr}, [Customr Name -= {'Cust1', 'Cust2', 'Cust3'}>}[Invoive Total]),1,1)

• ###### Re: Exclude multiple values from Dimension

Is it Cust1 or cust1? QlikView is case sensitive and you would want to make sure to use correct casing here. Try this:

=If(not Match(Lower([Customr Name]), 'cust1'), [Customr Name])

• ###### Re: Exclude multiple values from Dimension

Or a much better option would be to ignore a particular customr name in the expression itself:

Rank(Sum({\$<[Inv Month]={Apr}, [Customr Name -= {'Cust1'}>}[Invoive Total]),1,1)

or

Rank(Sum({\$<[Inv Month]={Apr}, [Customr Name ={'*'} - {'Cust1'}>}[Invoive Total]),1,1)

Use [Customr Name] as the dimensions