Count function with non-NULL and non-ZERO

[#1]

Hello,

I have 5 SALESGROUPS each with a large number of CLIENTS.

In the raw data some of the CLIENT'S REVENUE is either a

1. positive/negativer number
2. or hardcoded zero
3. blank.

I created a Straight Table with the SALESGROUP as the dimension and wanted only to COUNT THE NON-ZERO/NON-NULL CLIENTS.

I tried:

=if (revenue<>0 or revenue <> NULL(),count(distinct CLIENTS)) and some variants but not working.

Can anybody advise? Thanks in advance!

[#2]

Nulls can sometimes be difficult to work with. I would try to replace those values in the load script with either a 0 or -, if you need to tell the difference between 0 and blank. You should also try using Set Analysis. This may work:

REMOVED

That should get rid of the nulls and zero values.

EDIT: The expression pasted incorrectly. It has been removed. See below...

[#3]

Thanks for the reply.

I tried that expression but the expression comes up as an error (expression is default black font after the single quote).

I've never seen this type of expression before and am hoping there is some syntatical adjustment needed as I've no experience with the 0' type syntax.

Otherwise, I'm going to try to make a script adjustment like you suggested. Thanks again.

[#4]

Hi there, try this:

count($<revenue = {"*"} - {0}>distinct CLIENTS )

It is not tested but thats the main idea. The star ({"*"}) should get rid of null values, while (-{0}) should take the zero values out of the equation.

Regards

[#5]

Sorry, I forgot to mention that if you are using the revenue as an expression, similar to these:

sum(revenue)

You might try using as sum(aggr) type expression to count the values:

sum(aggr(if(sum(revenue) >0,1,0) ,Customer))

You can also use this notation to get rid of the if:

sum(aggr( (sum(revenue) > 0) * -1 ,Customer))

Regards

[#6]

Yeah, sorry, I was having some issues with the site earlier. That is not the expression I pasted in. The Edit option wasn't working and I kind of forgot about it.

The expression I was trying to post was:

Count({<revenue={'>0'}>+<revenue={'<0'}>} distinct CLIENTS)

That should give the list of all CLIENTS with a revenue greater than 0 or less than 0, which should exclude nulls and zeros.