Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
andrzej12
Contributor II
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
sunny_talwar

How about this

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

andrzej12
Contributor II
Contributor II
Author

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?

sunny_talwar

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
Contributor II
Contributor II
Author

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)

sunny_talwar

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
Contributor II
Contributor II
Author

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:

sunny_talwar

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

OmarBenSalem
Partner
Partner

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?

sunny_talwar

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