0 Replies Latest reply: Jan 24, 2017 4:25 PM by Jeff Estroff RSS

    Computing mean time between failure for weeks with no data

    Jeff Estroff

      I am a Qlik Sense user.  I am trying to compute a weekly (based on CheckinDate) mean time between failure rate (MTBF) as:
         
          No of devices * No of days (7 days in a week)
          ---------------------------------------------
                       No of crashes (if > 0)

       

      Here is a simple dataset to illustrate the problem:

       

      ID            StartupDate CheckinDate FailureCount
      deviceA   11/6/2016    1/1/2017        2
      deviceB   11/6/2016    1/1/2017        0
      deviceA   11/6/2016    1/8/2017        1
      deviceB   11/6/2016    1/8/2017        0
      deviceA   11/6/2016    1/15/2017      3
      deviceB   11/6/2016    1/15/2017      1
      deviceA   11/6/2016    1/22/2017      1
      deviceB   11/6/2016    1/22/2017      0

       

      For simplicity, I have reduced the dataset down to a single device.  So, for the week of 1/1/2017, the weekly MTBF rate is:

          2 devices * 7 days in a week
          ---------------------------    = 7.0
                 2 crashes

       

      When I load this data in a table, I am only able to compute the MTBF value for the weeks starting with the first checkin date (1/1/2017) going forward.  However, I would like to count all of those weeks between 11/1/2016 (StartupDate) and 1/1/2017 (first CheckinDate) as having no failures.

          2 devices * 7 days in a week
          ---------------------------    = 14.0
                1 (zero crashes)


      Do you have any suggestions on how to solve this problem?  The only way I can think of is to insert rows of data with a zero failure count for all of those weeks between 11/1/2016 and 1/1/2017 for each device?

       

      For example, the resulting table would look like this (newly inserted rows are shown in italics):

      ID            StartupDate CheckinDate FailureCount

      deviceA   11/6/2016    1/1/2017        2

      deviceB   11/6/2016    1/1/2017        0

      deviceA   11/6/2016    1/8/2017        1

      deviceB   11/6/2016    1/8/2017        0

      deviceA   11/6/2016    1/15/2017      3

      deviceB   11/6/2016    1/15/2017      1

      deviceA   11/6/2016    1/22/2017      1

      deviceB   11/6/2016    1/22/2017      0

       

      deviceA 11/6/2016 11/1/2017 0
      deviceB 11/6/2016 11/1/2017 0
      deviceA 11/13/2016 11/1/2017 0
      deviceB 11/13/2016 11/1/2017 0
      deviceA 11/20/2016 11/1/2017 0
      deviceB 11/20/2016 11/1/2017 0
      deviceA 11/27/2016 11/1/2017 0
      deviceB 11/27/2016 11/1/2017 0
      deviceA 12/4/2016 11/1/2017 0
      deviceB 12/4/2016 11/1/2017 0
      deviceA 12/11/2016 11/1/2017 0
      deviceB 12/11/2016 11/1/2017 0
      deviceA 12/18/2016 11/1/2017 0
      deviceB 12/18/2016 11/1/2017 0
      deviceA 12/25/2016 11/1/2017 0
      deviceB 12/25/2016 11/1/2017 0

       

      How could this be accomplished within the load script? or if you have another suggestion to solve the problem in a different way, let me know your thoughts!  Thank you, Jeff