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

# Computing mean time between failure for weeks with no data

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