Diagram with exclusion of values

[#1]

Hi everybody,

I have the following problem

I have a table with values like thisd: 

Name      certificateTyp  Date

x               1                     1.1.2017

x               2                     1.1.2017

y               3                     1.1.2017

y               1                     1.1.2017

z               2                     1.2.2017

b                         

Now I want to have all names that have no certificate type 2

Result:

Name    

y

b

I try this with a chart Table!

But I have no luck

thanks for your help!

[Oleg_Troyansky]

You can do it with Set Analysis:

First, the count of Names with Certificate type 2 can be calculated like this:

count({<CertificateType={2}>} Name)

Now, you are interested in those Names that have 0 for this count. You can either use an IF function:

count(

     IF ( count({<CertificateType={2}>} Name) = 0, Name)

)

Or, even better, you can enclose your condition in another Set Analysis condition, this time leveraging Advanced Search:

count({< Name = {"=count({<CertificateType={2}>} Name) = 0"} >} Name)

Use this as an Expression with the field Name as a Dimension, and you should get your result.

Come and learn advanced Set Analysis from my lecture at the Masters Summit for Qlik - coming to Boston, MA this October!

Cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

[Anil_Babu_Samineni]

Or This?

IF ( count({<certificateTyp={2}>} Name) = 0, Name)

[shiveshsingh]

hi

You can try below set analysis in chart

Count({$<Certification = {2}>}Name)

[Oleg_Troyansky]

Not quite... If you'd like to use this formula in a calculated dimension, you'd have to enclose it in the AGGR() function. Then it would work, but it would be more resource-heavy, compared to my earlier suggestions.

[Anil_Babu_Samineni]

Agree, I thought thread owner needs data as well so that i can use and think as expression. When Calculation Dimension required then there must be Aggr() as you suggest earlier. Thanks for the Message

[#7]

Thanks, that help!

[shuck1985]

Came on here to ask this question and so glad to find an answer straight away so thank you for asking and thanks to those helpful replies.