Include null in KPI set analysis expression

[Anonymous]

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?

[sunny_talwar]

How about this

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

[Anonymous]

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)

[Anonymous]

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)

[Anonymous]

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]

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?