Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

0 Replies