Maximo Open Forum

 KPI Creation

  • Functional
  • Work Management
Bill Poelma's profile image
Bill Poelma posted 04-04-2022 15:06
​​I am trying to create a KPI to show the number of days it takes to close CM's and PM's by month.  The thought process is to average the difference between the reported date and the actual finish date.  For the Where clause I have the following: where (status='CLOSE' or status='CRAFTCOMP') and (worktype='PM' or worktype='CM') and supervisor='WPOELMA' and (actfinish>getdate()-30) and (woclass='workorder') and istask=0 and siteid='WWTP'.  For the Select clause I have the following: SELECT AVG(DATEDIFF(d, actstart, actfinish)) FROM WORKORDER.  I am new to creating these KPI's and to the coding language so any help would be appreciated.  I cannot get these to work and am doing something majorly wrong.
Thanks!
Travis Herron's profile image
Travis Herron posted 04-05-2022 10:23
I'd start by suggesting you capitalize WORKORDER at ...  and (woclass='WORKORDER') and ...

If that doesn't fix it, can you elaborate on what results you see? You said it's not working -- does that mean it's throwing an error when you try to save it, or it saves but doesn't run; or it runs but the answer it returns doesn't make sense, etc.?
Nikolaus Despain's profile image
Nikolaus Despain posted 04-05-2022 12:09

MAXIMO can be finicky when it comes to SQL statements – not all SQL DATE functions work in MAXIMO.

Here are a couple of changes to your statements that have worked for me in the past…

For your Where clause change “…>getdate()-30)” to “…>(CURRENT DATE – 30 DAYS)”

where (status='CLOSE' or status='CRAFTCOMP') and (worktype='PM' or worktype='CM') and supervisor='WPOELMA' and (actfinish>(CURRENT DATE- 30 DAYS)) and (woclass='workorder') and istask=0 and siteid='WWTP'

For your select statement use “timestampdiff” (I changed your ACTSTART to REPORTDATE – as that is what you mentioned in your writing… (You may need to tweak the numbers used to divide by to get to the right value [i.e days or hours, etc…]

select avg(1.0*((timestampdiff(4, char(timestamp(actfinish)-timestamp(reportdate))))/60)/24) from workorder


Let me know if this solves the issues you are having…

Related Content