Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jennajo123
New Contributor

How to exclude null value in KPI or Pivot table.

Hi, All

I’d like to make KPI or Pivot table without Null value.

Example filter is as below.

I want to collect # of Status : Active, collect only if Certificate # (Column B) has value.

In this data, Value should be 3. Because Row 5 has no value.

But when I tried to make table, it show value as 4, including null value. Even I didn’t calculate.

Status – Active : 3


캡처.GIF

I tried not calculate null value. But it’s not work.

Would you share your wisdom ?

Thanks!

4 Replies

Re: How to exclude null value in KPI or Pivot table.

Then it's probably not a null value, but an empty string or one or more spaces. Try count({<[Certificate #]={">0"}]>}Status)

dwforest
Valued Contributor

Re: How to exclude null value in KPI or Pivot table.

If it is coming into Qlik as null

count({<[Certificate #]=*>}Status)

This only counts non-null values.

Re: How to exclude null value in KPI or Pivot table.

Or this?

count({<[Certificate #]={"=Len([Certificate #])>0"}]>}Status)

pawwy1415
Contributor II

Re: How to exclude null value in KPI or Pivot table.

Try this

=Count({<Status={'Active'},[Certificate#]={"*"}-{' '}>} enterfield)