Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

andrzej12
New Contributor II

Include null in KPI set analysis expression

Hello,

I have following query

select count(wo.WORKORDERID)

from workorder wo

left join ws….

left join cd….

where (ws.ISOVERDUE=1)

and ((cd.CATEGORYNAME = 'AA')

  OR (cd.CATEGORYNAME = 'BB')

  OR (cd.CATEGORYNAME IS NULL));

I want to write KPI based on above query. KPI is OK when I count:

Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB'}>} Distinct WORKORDERID)

But this version is wrong:

Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB', IsNull}>} Distinct WORKORDERID)

I have tried also these versions below and they are wrong too:

Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB'}> + < [ISOVERDUE]={'1'}, WORKORDERID={'=Len(Trim(CATEGORYNAME))=0'}>} Distinct WORKORDERID)

Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB'}> + < [ISOVERDUE]={'1'}, CATEGORYNAME ={'-'}>} Distinct WORKORDERID)

Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB'}> + < [ISOVERDUE]={'1'}, CATEGORYNAME -={'*'}>} Distinct WORKORDERID)

How to include null or blank values in KPI calculation?

11 Replies
MVP
MVP

Re: Include null in KPI set analysis expression

How about this

Count({$<[ISOVERDUE] = {'1'}, WORKORDERID = {"=Match(CATEGORYNAME, 'AA', 'BB') or Len(Trim(CATEGORYNAME)) = 0"}>} DISTINCT WORKORDERID)

andrzej12
New Contributor II

Re: Include null in KPI set analysis expression

Thank you Sunny, great help, I will werify it soon.

Now I would like to add to my count more conditions.

Query will look like below:

select Count(wo.WORKORDERID)

from workorder wo

left join ws….

left join cd….

where (ws.ISOVERDUE=1)

AND((cd.CATEGORYNAME = 'AA')

      OR (cd.CATEGORYNAME = 'BB')

      OR (cd.CATEGORYNAME IS NULL))

AND ((QUEUENAME != ‘FACIL')

     AND (QUEUENAME != 'WATTO')

     AND (QUEUENAME != 'HELP')

     OR (QUEUENAME IS NULL));

Combining your answear and my new conditions KPI may look as below:

Count({$<[ISOVERDUE] = {'1'}, WORKORDERID = {"=Match(CATEGORYNAME, 'AA', 'BB') or Len(Trim(CATEGORYNAME)) = 0"}>*

(< [QUEUENAME]-={'FACIL'}, [QUEUENAME]-={'WATTO'}, [QUEUENAME]-={'HELP'}>+<[QUEUENAME]={IsNull}>)

} DISTINCT WORKORDERID)

but again it is something wrong.

Can I somehow implement Match function for new condition or is another way to solve it?

MVP
MVP

Re: Include null in KPI set analysis expression

May be just do this again within the same WORKORDERID modifier

Count({$<[ISOVERDUE] = {'1'}, WORKORDERID = {"=(Match(CATEGORYNAME, 'AA', 'BB') or Len(Trim(CATEGORYNAME)) = 0) and (not Match(QUEUENAME, 'FACIL', 'WATTO', 'HELP') or Len(Trim(QUEUENAME)) = 0)"}>} DISTINCT WORKORDERID)

andrzej12
New Contributor II

Re: Include null in KPI set analysis expression

It works regarding  MATCH() but it doen's work regarding Len(Trim()).

I have simplified query and KPI for easier analysis.

Query looks as below and gives around 60 records from my DB:

select Count(wo.WORKORDERID)

from workorder wo

left join ws on …

left join cd on …

where (ws.ISOVERDUE=1)

and (cd.CATEGORYNAME IS NULL);

KPI looks as below and it counts over 1200 records:

Count({$< [ISOVERDUE]={'1'}, WORKORDERID={"= Len(Trim(CATEGORYNAME)) = 0"}>} Distinct WORKORDERID)

MVP
MVP

Re: Include null in KPI set analysis expression

The only thing I see a little different is that your where clause include fields which are from other tables (ws and cd)... are you left joining these tables in your app? If not, can you check what this gives?

Count(DISTINCT If([ISOVERDUE] = 1 and Len(Trim(CATEGORYNAME)) = 0, WORKORDERID)

andrzej12
New Contributor II

Re: Include null in KPI set analysis expression

The count:

Count(DISTINCT If([ISOVERDUE] = 1 and Len(Trim(CATEGORYNAME)) = 0, WORKORDERID)

gave 1260 records.

Below is part of the data model from model viewer with appropriate tables:

MVP
MVP

Re: Include null in KPI set analysis expression

Very difficult to say... would you be able to share a sample?

omarbensalem
Esteemed Contributor

Re: Include null in KPI set analysis expression

why did use the "=" before the first match( which is logical to me) and did not proceed with same logic with the len(trim()) part?

MVP
MVP

Re: Include null in KPI set analysis expression

Is this for me? I am sorry, I am not sure what you are asking?