topic Count function with non-NULL and non-ZERO in QlikView

Count function with non-NULL and non-ZERO

Wed, 26 Jan 2011 15:42:51 GMT

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!

Count function with non-NULL and non-ZERO

Wed, 26 Jan 2011 16:30:47 GMT

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...

Count function with non-NULL and non-ZERO

Wed, 26 Jan 2011 16:40:16 GMT

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.

Count function with non-NULL and non-ZERO

Wed, 26 Jan 2011 16:49:51 GMT

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

Count function with non-NULL and non-ZERO

Wed, 26 Jan 2011 16:59:39 GMT

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

Count function with non-NULL and non-ZERO

Wed, 26 Jan 2011 22:42:18 GMT

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.