topic COUNT expression help in QlikView

COUNT expression help

Fri, 04 Feb 2011 12:58:38 GMT

Hello,

Does Count() include NULLS? If no I have the following problem.

I want to Count all my clients where the total portfolio is <>0 for a selected YearMonth. I've used the following expression

IF(Sum ({$<[YearMonth] = {$(SelectedYearMonth)}>} Value) <> 0, COUNT(DISTINCT(Client)), NULL() )

To test I've created a chart with dimension 'Client' and the expression above to count. So the Count column displays '1' or '-' per client depending on the result of the expression.

My problem is that the Total Count seems to be including the '-' in Total so it is essentially giving me a SUM of the number of rows. I've used 'Expression Total' in the Total Mode.

Is there a way to ignore the NULL in when using COUNT?

All help is appreciated

COUNT expression help

vgutkovsky — Fri, 04 Feb 2011 18:35:21 GMT

Count does not include nulls. Your problem is that you're counting Clients, and this does not take into account your sum expression. You need to use advanced aggregation for this (aggr). I'm assuming you know your set analysis is correct, so here is what the aggr would look like:


sum(
 aggr(nodistinct
 if(sum({<[Year Month]={$(SelectedYearMonth)}>} Value) <>0,1)
 ,Client
 )
)

The aggr essentially creates flags of 1 for each client where sum(value)<>0 and the outer sum adds the total of these flags.

Cheers,

COUNT expression help

Tue, 08 Feb 2011 15:40:12 GMT

Thank you for the code and explanation Vlad, you've helped a great deal!