Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try something like
=NullCount(value) + Count(if(value<400,value))
or using set analysis
=NullCount(value) + Count({<value = {"<400"} >} value)
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.
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