count null and non null values

[yelin_nyu]

If I have a table like below

product  week value

1,           1,       null

1,           2,       123

2,           1,        333

2,           2          444

say I want to count values below 400 including nulls. I should get 3. how to include null values in counting?

Thanks

[swuehl]

Try something like

=NullCount(value) + Count(if(value<400,value))

or using set analysis

=NullCount(value) + Count({<value = {"<400"} >} value)

[yelin_nyu]

does nullcount() work in script? I am trying to say if( values <400 or len(values)=0, 'green') as color_flag.

And does not count the null values.

[swuehl]

You can use Nullcount in the script, but as with all aggregation functions, you probably need to use a group by clause.

If you just want to create a flag if value is <400 or NULL, you don't need a Nullcount or any other aggregation function.

Try

     if(values < 400 or len(trim(values))=0,'green') as color-flag

the len(trim()) will take care of NULLs and blanks. There is also an isnull() function you can use (but I prefer len(trim()), it's more robust).

Regards,

Stefan