# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor

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

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.

1 Solution

Accepted Solutions
MVP

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

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
Sys124,548611
Sys240,695833
Sys328,642361
Sys475,874653
Sys50,99930556
Sys68,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.

6 Replies
MVP

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

Can you may be try to trim down your sample?

Preparing examples for Upload - Reduction and Data Scrambling

MVP

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

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
Sys124,548611
Sys240,695833
Sys328,642361
Sys475,874653
Sys50,99930556
Sys68,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.

New Contributor

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

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,

D

MVP

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

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!

New Contributor

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

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,

D

MVP

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

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.

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