COUNT expression help

[#1]

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

G

[vgutkovsky · Accepted solution]

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,

[#3]

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

G