Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
OmarBenSalem

Yes : I was refering to this :

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


I was exepecting :

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


No?

sunny_talwar

The equal sign is for search string and you only need a single equal sign...

This is the search string

Match(CATEGORYNAME, 'AA', 'BB') or Len(Trim(CATEGORYNAME)) = 0

and you just need a single equal sign. Think of this as an expression you are using against WORKORDERID... where the above expression gives -1, those WORKORDERIDs will be included... where it gives 0... those will be excluded....

In general, search string is a boolean expression checking if the value is true or false.

I might have explained more than what you asked... but hope it helps.

Best,

Sunny