Maximo Open Forum

 View Only

 Why is this KPI not functioning

  • Analytics
Russell Lum's profile image
Russell Lum posted 04-05-2022 10:01
I created the following percentage KPI:

select (select count(*) from workorder where (woclass = 'WORKORDER' and siteid = '500' and (status = 'CLOSE' or status = 'FMCOMP') and reportdate >= trunc(sysdate, 'MM') and istask = 0 and (worktype = 'PM' or worktype = 'PMCAL'))) / (select count(*) from workorder where (woclass = 'WORKORDER' and siteid = '500' and (status = 'READYTW' or status = 'WAPPR' or status = 'WSCH' or status = 'WPLAN' or status = 'COMP' or status = 'INPRG' or status = 'CLOSE' or status = 'FMCOMP') and reportdate >= trunc(sysdate, 'MM') and istask = 0 and (worktype = 'PM' or worktype = 'PMCAL'))) *100 from dummy_table

It is returning 0.  The first select statement on its own returns 31, and the 2nd returns 418.  So, in theory this entire statement should return 7.4.  Not sure what is wrong with this.
Travis Herron's profile image
Travis Herron posted 04-05-2022 10:12
In your testing -- as in "the first select statement on its own returns 31" -- was that done directly on the database, or did you make a KPI for that statement alone?

What I'm getting at is, I suspect the KPI isn't dealing with your trunc statement properly.  The database understood it, but the KPI doesn't handle it properly.
Russell Lum's profile image
Russell Lum posted 04-05-2022 10:21
I tested the two individual statements (x & y) by copying the where portion into work order tracking.  They functioned properly.  I also created two decimal KPIs using x & y independently, and the also functioned properly that way.  The issue comes when I combine them into: select x/y *100
Travis Herron's profile image
Travis Herron posted 04-05-2022 10:36
Just a stretch at this point then:  Have a look at what I believe was an out-of-the-box KPI, EMWNODOWN.

select ((select count(*) from workorder where status='CLOSE' and istask=0 and worktype='EM' and downtime=0)*.1)/ ((select count(*) from workorder where status='CLOSE' and istask=0 and worktype = 'EM')*.1)*100 from dummy_table

Notice that they multiplied both the numerator and denominator by .1 -- maybe you could try that?
Russell Lum's profile image
Russell Lum posted 04-05-2022 10:47
Well...  that did it.  I can't say that I understand why, but it works.  Thanks. 

If anyone else knows why the *.1 fixed this please explain.
Travis Herron's profile image
Travis Herron posted 04-05-2022 10:58
I'll take a guess. . .count() returns an integer; multiplying by .1 implicitly changes it to decimal, and I'm guessing the "plumbing" behind Maximo's KPI Percentage calculation needs decimal datatype values to work.

Related Content