Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
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 (3)
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])

View solution in original post

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