Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ClementByr
Contributor III
Contributor III

Count if with count if condition

Hello everyone,

I wonder if it possible to do a count if with a count if condition.

Here is a table showing people doing differents activities :

NameActivity
JeanFootball
JeanCinema
JeanJogging
JeanBeer
MarieTennis
MarieCinema
MarieBoxe
DanielJogging
CecileGolf
CecileTennis
CecileCinema
CecileShopping
CecileJogging
NathanTennis
NathanJudo
NathanCinema

 

I want to know the number of people who have exactly 3 activities. The results is supposed to be 2 since there are 2 people who have exactly 3 activities (Marie and Nathan).

I believe a count if with a set analysis is the way to do it, but I can't manage to make it work.

Here what I tried : Count(distinct{<Count(distinct[Activity])='3'>}[Name])

Does anyone know if there is formula to solve this ? Thank in advance.

Best regards.

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Count(DISTINCT {< Name = {"=Count(DISTINCT [Activity]) = 3"}>} [Name])

View solution in original post

7 Replies
sunny_talwar

Try this

Count(DISTINCT {< Name = {"=Count(DISTINCT [Activity]) = 3"}>} [Name])

ClementByr
Contributor III
Contributor III
Author

Thank you, it works. Smiley Happy

jack95
Contributor II
Contributor II

This could help with something I'm currently stuck on. Could the =3 be replaced by >variable? I'm having no luck with that

sunny_talwar

I would think so... can you share an example of where this doesn't work?

jack95
Contributor II
Contributor II

Hi Sunny, so using your original solution I am able to make this work:

=Count(DISTINCT {< [Customer ID] = {"=


(sum({$<[Sales Type] = {'Classified'},UniqueEvent={'view'}>}EventCount)/sum({$<[Sales Type] {'Classified'}>}VacancyCount))

>

180

"}>} [Customer ID])

and that counts the number of customers where the calculation is greater than 180

What I can't manage is replacing the 180 with a second calculation so it looks like this:

=Count(DISTINCT {< [Customer ID] = {"=


(sum({$<[Sales Type] = {'Classified'},UniqueEvent={'view'}>}EventCount)/sum({$<[Sales Type] {'Classified'}>}VacancyCount))

>

sum({$<[Customer ID]= E([Customer ID]),[Sales Type] = {'Classified'},UniqueEvent={'view'}>}EventCount)/sum({$<[Customer ID]= E([Customer ID]),[Sales Type] = {'Classified'}>}VacancyCount)

"}>} [Customer ID])

that gives me 0, even though the value of the second calculation in this case = 180

sunny_talwar

May be you need TOTAL qualifier

=Count(DISTINCT {< [Customer ID] = {"=


(Sum({$<[Sales Type] = {'Classified'}, UniqueEvent = {'view'}>} EventCount)/Sum({$<[Sales Type] = {'Classified'}>} VacancyCount))

>

Sum(TOTAL {$<[Customer ID] = E([Customer ID]), [Sales Type] = {'Classified'}, UniqueEvent = {'view'}>} EventCount)/Sum({$<[Customer ID] = E([Customer ID]),[Sales Type] = {'Classified'}>} VacancyCount)

"}>} [Customer ID])
jack95
Contributor II
Contributor II

That worked, thanks Sunny 🙂

For anyone else wondering, in my case I changed both the numerator and the denominator from sum( to sum(total, though perhaps doing just one would work