topic Re: How do you calculate Mean Time Between Impacts(hour/time)? in QlikView

How do you calculate Mean Time Between Impacts(hour/time)?

Anonymous — Mon, 01 Aug 2016 19:29:38 GMT

How do you write the expression & dimension for showing Mean Time Between Impacts (MTBI)? Impact = count(impact_enum).

Assuming apart of the calculation is impact_end_time - impact_start_time . I want to have either hours or days sum represented on the graph by month, quarter and year.

Re: How do you calculate Mean Time Between Impacts(hour/time)?

sunny_talwar — Mon, 01 Aug 2016 19:59:28 GMT

Can you may be try to trim down your sample?

Preparing examples for Upload - Reduction and Data Scrambling

Re: How do you calculate Mean Time Between Impacts(hour/time)?

swuehl — Mon, 01 Aug 2016 20:33:06 GMT

I assume you want to calculate something like a MTBF

Mean time between failures - Wikipedia, the free encyclopedia

One way to calculate this would be (assuming you want to calculate the number per service type and the data loaded in is sorted in chronological order):

Impact Affected Service	=Avg(Aggr( [Impact Start Time] -Above([Impact End Time]), [Impact Affected Service], [Impact Number]))
	31,787202
Sys1	24,548611
Sys2	40,695833
Sys3	28,642361
Sys4	75,874653
Sys5	0,99930556
Sys6	8,3048611

Numbers in fraction of days, you can use Interval() function to show your numbers in hours or minutes.

You can also calculate your numbers in your load script, if you want.

Re: How do you calculate Mean Time Between Impacts(hour/time)?

Anonymous — Mon, 01 Aug 2016 23:43:42 GMT

I almost have it working. But I have an issue with end time being earlier than 2016 due to user inputting bad data in the input field . Example =Avg(Aggr( [impact_start_time] -Above([impact_end_time])

7/21/2016 6:43:38 AM - 1/1/1970

Is there an expression that I can disregard the 1970 end time date and any input of an end time date that proceeds the start time date and vice versa? That way it doesn't mess up the MTBF.

Thanks,

Re: How do you calculate Mean Time Between Impacts(hour/time)?

swuehl — Tue, 02 Aug 2016 06:19:35 GMT

You can try to correct or filter the records in the script (e.g. using a flag) or in the UI using e.g. set analysis like

=Avg(

{<[Impact Number] = {"=[Impact Start Time]<= [Impact End Time]"}>}

Aggr( [Impact Start Time] -Above([Impact End Time]), [Impact Affected Service], [Impact Number]))

or maybe

=Avg(

{<[Impact End Time] -= {'1/1/1970'}>} // use the correct timestamp code for your value you want to filter as shown in QV

Aggr( [Impact Start Time] -Above([Impact End Time]), [Impact Affected Service], [Impact Number]))

This will filter the impact numbers WHERE Start Time <= End Time.

Note two things:

- Removing impact numbers will over-estimate your MTBF, then. Maybe it's better to just set the end time to start time for these impacts in the script.

- Using the Aggr() function requires your Impact Numbers loaded in in chronological order, or use structured parameter:

The sortable Aggr function is finally here!

Re: How do you calculate Mean Time Between Impacts(hour/time)?

Anonymous — Mon, 08 Aug 2016 23:46:16 GMT

Hello,

Hopefully this is my last issue on this question but with aggr function can I through in a parameter of >120 days? Being a newbie still not sure how to use aggr. I am trying to make MBTF have a definition of

Max. acceptable period between Top or High failures

Any insight on this is much appreciated.

Thanks,

Re: How do you calculate Mean Time Between Impacts(hour/time)?

swuehl — Tue, 09 Aug 2016 18:24:42 GMT

Not sure if I really understand what you are asking for.

Using e.g. set analysis should enable you to filter your data, also in the advanced aggregation.

Set Analysis in the Aggr function‌

The Aggregation Scope

A Primer on Set Analysis

Note that where I have used a pure field reference in the expression part of the advanced aggregation, it should be read as an implicite Only(FIELD). And remember that you can apply set analysis to Only() function.

The Only Function