topic Re: Count if with count if condition in App Development

Count if with count if condition

ClementByr — Sat, 16 Nov 2024 05:57:47 GMT

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 :

Name	Activity
Jean	Football
Jean	Cinema
Jean	Jogging
Jean	Beer
Marie	Tennis
Marie	Cinema
Marie	Boxe
Daniel	Jogging
Cecile	Golf
Cecile	Tennis
Cecile	Cinema
Cecile	Shopping
Cecile	Jogging
Nathan	Tennis
Nathan	Judo
Nathan	Cinema

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.

Re: Count if with count if condition

sunny_talwar — Tue, 30 Apr 2019 13:08:32 GMT

Try this

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

Re: Count if with count if condition

ClementByr — Tue, 30 Apr 2019 13:23:45 GMT

Thank you, it works.

Re: Count if with count if condition

jack95 — Thu, 08 Aug 2019 20:06:49 GMT

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

Re: Count if with count if condition

sunny_talwar — Fri, 09 Aug 2019 01:34:14 GMT

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

Re: Count if with count if condition

jack95 — Sat, 10 Aug 2019 10:52:21 GMT

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

Re: Count if with count if condition

sunny_talwar — Mon, 12 Aug 2019 01:21:58 GMT

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])

Re: Count if with count if condition

jack95 — Mon, 12 Aug 2019 18:18:53 GMT

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