topic NULL values not being counted in QlikView

NULL values not being counted

Mon, 14 Oct 2013 17:48:48 GMT

Relating to the same tables on this thread: http://community.qlik.com/thread/95797

Imagine I have a row with NULL value in Days Bucket column. This NULL value comes from an excel sheet with an empty cell.

So for my pivot table I have an option to show an extra column for NULL values of Days Bucket.

The bad thing is that in the Count([Days Bucket]) in the sub column for NULL Days Buckets it shows 0 even if there are some rows with empty(NULL) Days Bucket.

How can I fix this?

Thanks,

Miguel

Re: NULL values not being counted

Mon, 14 Oct 2013 17:55:55 GMT

Hello Miguel
You can apply a value to the null columns on your script that does not affect your calculations (either 0 or or a letter) so when you count those values the NULLS will be included and when you do other operations with the field (like sum or avg), it won't affect the result
Something like If(IsNull([Days Bucket]),0,[Days Bucket]) as [Days Bucket]
Hope it helps

Pablo

Re: NULL values not being counted

Gysbert_Wassenaar — Tue, 15 Oct 2013 06:12:46 GMT

Not sure what you're trying to do. Perhaps it's as simple as disabling the Suppress Zero Values on the Presentation tab of the properties window. Or if you want another expression to count the nulls: nullcount([Days Bucket]).

Perhaps if you posted an example document with enough sample data to demonstrate the issue somebody can create a solution for you.

Re: NULL values not being counted

Tue, 15 Oct 2013 08:16:08 GMT

Count(if(IsNull([Days Bucket]), 1, [Days Bucket]))

I don't know if this is a good solution, but it worked.

I don't understand why QlikView don't do this automatically...

Re: NULL values not being counted

Tue, 15 Oct 2013 09:44:52 GMT

Instead of Count(if(IsNull([Days Bucket]), 1, [Days Bucket]))

just did

Count(Full Text Reason Code)

because there are no empty columns in Full Text Reason Code.