topic Re: Count a count expression? in QlikView

Count a count expression?

Fri, 13 May 2011 13:19:31 GMT

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

Count a count expression?

erichshiino — Fri, 13 May 2011 13:29:58 GMT

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

Count a count expression?

rbecher — Fri, 13 May 2011 13:31:29 GMT

This works:

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

- Ralf

Re: Count a count expression?

Fri, 13 May 2011 13:43:51 GMT

Hello,

and just another suggestion:

Regards, Roland

Count a count expression?

Fri, 13 May 2011 13:54:09 GMT

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

Re: Count a count expression?

thanstad — Tue, 22 Oct 2013 06:02:36 GMT

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

/Tormod