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: 
Not applicable

Count a count expression?

CustomerSport
ARugby
AFootball
ATennis
ACricket
BFootball
BTennis
CFootball
DTennis
ERugby
FTennis
FFootball
GTennis

Hello, what I want to do is count the number of times a customer only has 1 sport against them, regardless of what the sport actually is. So customer A has 4 sports against them, customer B has 2 sports against them, Customers C-E have only 1 sport against them, customer F has 2 sports against them and customer G has 1 sport against them. So the answer is 4 (only 4 customers have only one sport against them). Essentially its a count of a count, but I have no idea how this is done. Any help will be much appreciated.

Thanks,


Ralph

1 Solution

Accepted Solutions
rbecher
MVP
MVP

This works:

=count({$<Customer={"=count(DISTINCT Sport)=1"}>} DISTINCT Customer)

- Ralf

Astrato.io Head of R&D

View solution in original post

5 Replies
erichshiino
Partner - Master
Partner - Master

Hi, Try this (didn`t check, but should look like this)

= sum ( aggr ( if( count( Sport)= 1 , 1, 0)

, Customer ) )

//maybe you should usecount(distinct Sport) - depends on your real table

This will group the data by Customer. For each one of them, it gives one or zero according to the condition.

After this I sum the 1s.

Hope it helps,

Erich

rbecher
MVP
MVP

This works:

=count({$<Customer={"=count(DISTINCT Sport)=1"}>} DISTINCT Customer)

- Ralf

Astrato.io Head of R&D
Not applicable
Author

Hello,

and just another suggestion:

Regards, Roland

Not applicable
Author

Many thanks for all these suggestions, they all work as expected. Thanks, Ralph

thanstad
Creator
Creator

This solution is very useful for my work where we have different ways of DISTINCT count.

/Tormod