Computing mean time between failure for weeks with no data

Tue, 24 Jan 2017 21:25:37 GMT

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

topic Computing mean time between failure for weeks with no data in App Development

Computing mean time between failure for weeks with no data