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
