Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jisephcirspy
Contributor III
Contributor III

count, distinct, if

So I want to count the entries where the state is 'CRIT', but distinct the entries with the same names.
It is a bit like this:

count(DISTINCT[name]) but with the condition If(state='CRIT', 1, 0))

 

 

If(state='CRIT', 1, 0))

 

 

 

count(DISTINCT[name]) 

 

 

Labels (3)
2 Solutions

Accepted Solutions
stevedark
MVP
MVP

Hi @jisephcirspy 

You have all the bits you need there, just need to put them together.

The thing you can use to your advantage is that the COUNT function (DISTINCT or otherwise) does not count NULLs, so you just have to put a null in where it is not the type you wish to count:

count(DISTINCT If(service_state='CRIT', alias, null()))

 

That should do the trick.

Steve

View solution in original post

dieterwoestemeier
Contributor II
Contributor II

Try to use set analysis where possible because it performs way better than IFs.

In your case:

count({<state={'CRIT'}>} DISTINCT([name]))

View solution in original post

4 Replies
stevedark
MVP
MVP

Hi @jisephcirspy 

You have all the bits you need there, just need to put them together.

The thing you can use to your advantage is that the COUNT function (DISTINCT or otherwise) does not count NULLs, so you just have to put a null in where it is not the type you wish to count:

count(DISTINCT If(service_state='CRIT', alias, null()))

 

That should do the trick.

Steve

View solution in original post

jisephcirspy
Contributor III
Contributor III
Author

Thank you very much @stevedark !!! helped me a lot

dieterwoestemeier
Contributor II
Contributor II

Try to use set analysis where possible because it performs way better than IFs.

In your case:

count({<state={'CRIT'}>} DISTINCT([name]))

View solution in original post

stevedark
MVP
MVP

Hi @dieterwoestemeier 

D'oh! Focussed on answering the question in hand that I missed that there was a much better way of achieving the same thing.

Thanks for posting a better way.