Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Customer | Sport |
---|---|
A | Rugby |
A | Football |
A | Tennis |
A | Cricket |
B | Football |
B | Tennis |
C | Football |
D | Tennis |
E | Rugby |
F | Tennis |
F | Football |
G | Tennis |
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
This works:
=count({$<Customer={"=count(DISTINCT Sport)=1"}>} DISTINCT Customer)
- Ralf
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
This works:
=count({$<Customer={"=count(DISTINCT Sport)=1"}>} DISTINCT Customer)
- Ralf
Hello,
and just another suggestion:
Regards, Roland
Many thanks for all these suggestions, they all work as expected. Thanks, Ralph
This solution is very useful for my work where we have different ways of DISTINCT count.
/Tormod