3 Replies Latest reply: Nov 2, 2016 6:40 PM by kewing key RSS

    Calculate KPI

    kewing key

      Hello everyone,

      I'm a newbie here and thank you in advance for your help.

      The main goal is to have the number of "Met" with few conditions.

      Raw data:

      Request_IDKPIElapsedTime
      HD001Met0
      HD002Met0
      HD003Missed149545
      HD004Missed0
      HD005Pending0

      Met = KPI OK

      Missed = KPI NOK

      Pending = Pending (and some other indicators ...)

      I want to have all the "Met" and just the "Missed" with a value of ElapsedTime fulfilled with these conditions:

      Based on 43,200 seconds = 1 day.

      If greater than 216,000 sec then it's "Missed" and less than or equal to 216,000 sec then "Met"

      With few examples on the forum, I can determine the number of "Met" among all other KPI:

      Count ({<KPI = { 'Met'}>} DISTINCT request_id) / (Count ({<KPI = { 'Missed'}>} DISTINCT request_id) + count ({<KPI = { 'Met'}>} DISTINCT request_id ))

      This calculation is wrong because I have 50%. It counts 2 "Met" / 4 cases ("Met" and "Missed").

      I don't want the "Pending" status in the division.

      Reasoning: 149545 <216000 then "Met"

      Result: so we have 3 "Met" on 4 "request_id" case, so the value should be 75%.

       

      Thanks all for your help.

        • Re: Calculate KPI
          Sarah Rosen

          I'm confused by your question... "I wish I had all the Met + Missed" value of elapsed time?

           

          You can use set anaylsis such as this: 

           

          If you want to add up all the values you can use Sum({<KPI = {'MET'}>}ElapsedTime)     and also do that for "Missed" You can also add those two values, or put them in the same set analysis.

           

          But then you say that it should be 216,000 is missed, but 149545 is showing missed.  If this is the case then you will need to do that on your load script

           

          if (ElapsedTime > 216000, 'Missed' , 'Met" ) as KPI  

          • Re: Calculate KPI
            Ravi Kancharla

            Hi Kewing,

             

            If i understand your question correctly,

             

            1. You need to derive a another flag based on the time interval.

            2. If you wanted at KPI level, then there is way to aggregate at KPI field wise and link that table to your original table.

             

            Send us output for the above example in image and will get you the script for the same..

             

            Thank you

             

            ~Ravi Kancharla

             

            ,