3 Replies Latest reply on Jul 25, 2018 1:20 AM by Malene Fangel


    Malene Fangel

      I have a dataset, where three of the columns is ID, StartTime and EndTime.

      I want to calculate the downtime of a proces.
      If the proces is down, then there will be a row with an ID, Start and EndTime.

      If two ID's has the same Start and EndTime, the downtime should only be the DATEDIFF('seconds',startTime,endTime) of one of them and not sum them both together.

      In a timeline, it could look like this, and the downtime that I have now is the sum of all the differences.


      The long, red thin line, shows that 5 ID's has the same start and endTime, but the calculated downtime should only sum up one of them.

      Can anybody help me, to come up with a calculation?

        • 1. Re: Downtime
          Naveen B

          Hi Malene,


          In order to understand your requirement i have created the sample dataset as like below with one process and multiple ID



          If i use the datediff i am getting for every id seconds are getting repeated but what you need is 172800 instead sum of all 172800



          Change your calculation slightly, using max function now you can see only 172800 sec instead of sum



          hope this helps kindly mark this answer as correct or helpful so that it will help others


          If any doubts kindly attach twbx with sample data.  so that it will be great to help more




          • 2. Re: Downtime
            Malene Fangel

            Hey Naveen.


            This almost answer my question, but the problem is, that I can have other faults with the same ID but with a later time (as indicated on the thick red line above).


            So in you dataset, there would be another row with ID 1, start time 7:50:05 PM and end time 7:55:15 PM.

            When this is the case, it will only choose the downtime for the last fault.

            • 3. Re: Downtime
              Malene Fangel

              I figured it out by {FIXED [startTime] : MIN(DATEDIFF('second', StartTime, EndTime) ) }


              Now I have another problem:

              My data looks like this



              I want the total downtime to go from 00:00:00 to 00:02:55 and to be 2 minutes and 55 seconds plus the downtime from 11:12:42 to 11:15:10

              Right now I get the downtime to be 2 minutes and 54 seconds for ID 1 plus 2 minutes and 39 seconds for ID 2 plus the downtime for ID 3.


              Is there a way to compare the two rows Start and EndTime?

              In my data I have a lot of rows, and the optimal would be if I could compare all the rows.