topic Re: Include null in KPI set analysis expression in Visualization and Usability

Include null in KPI set analysis expression

Anonymous — Mon, 11 Jun 2018 12:10:32 GMT

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?

Re: Include null in KPI set analysis expression

sunny_talwar — Mon, 11 Jun 2018 12:14:06 GMT

How about this

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

Re: Include null in KPI set analysis expression

Anonymous — Mon, 11 Jun 2018 14:45:53 GMT

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?

Re: Include null in KPI set analysis expression

sunny_talwar — Mon, 11 Jun 2018 14:51:40 GMT

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)

Re: Include null in KPI set analysis expression

Anonymous — Tue, 12 Jun 2018 07:48:47 GMT

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)

Re: Include null in KPI set analysis expression

sunny_talwar — Tue, 12 Jun 2018 08:57:35 GMT

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)

Re: Include null in KPI set analysis expression

Anonymous — Tue, 12 Jun 2018 11:35:13 GMT

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:

Re: Include null in KPI set analysis expression

sunny_talwar — Tue, 12 Jun 2018 11:49:37 GMT

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

Re: Include null in KPI set analysis expression

OmarBenSalem — Tue, 12 Jun 2018 11:54:36 GMT

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?

Re: Include null in KPI set analysis expression

sunny_talwar — Tue, 12 Jun 2018 11:56:22 GMT

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

Re: Include null in KPI set analysis expression

OmarBenSalem — Tue, 12 Jun 2018 11:59:27 GMT

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?

Re: Include null in KPI set analysis expression

sunny_talwar — Tue, 12 Jun 2018 12:03:15 GMT

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