# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

## Average from specific field

Hi,

Thanks to Stefan and Rob, I have added in a new Dimension into my straight chart and called it Duration.  The Duration field contains the following expression:

=Interval(

(RangeMin(frac(closedate), MakeTime(18))

- RangeMax(frac(logdate), MakeTime(8)))

+ (NetWorkDays(logdate, closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)

The above expression basically takes the closedate from the logdate and then only calculates hours between 08:00 - 18:00 on a weekday.

As I have added this into my straight_table dimension, it pulls through what I want onto the straight_table.  However what I want to do is place a text box underneath the straight_table that calculates the average of this Duration Field (which is only created in the straight_table), depending on a set of values that I selected.  Therefore I may select 3 values with a duration of 01:00:00, 02:00:00, 03:00:00, and in the text field I want it to show the average time as 02:00:00.

Is it possible to calculate and average if the new Duration field was only created in a straight chart?

1 Solution

Accepted Solutions
MVP

## Re: Average from specific field

I think the order of dates in networkdays() function in the text object expression is incorrect, it should look like

=

Interval(

avg(

(RangeMin(frac(closedate), MakeTime(18))

- RangeMax(frac(logdate), MakeTime(8)))

+ (NetWorkDays(logdate, closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)

)

7 Replies
MVP

## Re: Average from specific field

Well, I think you need to replicate this calculation and add an avg() function:

=Interval(avg(

(RangeMin(frac(DT2), MakeTime(18))

- RangeMax(frac(DT1), MakeTime(8)))

+ (NetWorkDays(DT1, DT2-1) * MakeTime(10)) // Only 10 hours per whole day

))

This expression will regard your selections, so if you select three records, you will get the avg of those three records.

Not applicable

## Re: Average from specific field

Hi Stefan,

Thanks for the expression.  It works when I select manually, however if I select a month of data (eg: All date for August 2012, it shows a negative figure.

MVP

## Re: Average from specific field

Could you upload a small sample? Or modify one of the previous samples to match your setting?

Not applicable

## Re: Average from specific field

Hi Stefan,

I have attached a copy of the qvd file.  At the bottom of the Straight_Chart labelled All Close calls for Business Systems Team, there are two text objects, it is the one next to AVERAGE =.

Regards,

Jon

MVP

## Re: Average from specific field

I think the order of dates in networkdays() function in the text object expression is incorrect, it should look like

=

Interval(

avg(

(RangeMin(frac(closedate), MakeTime(18))

- RangeMax(frac(logdate), MakeTime(8)))

+ (NetWorkDays(logdate, closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)

)

Not applicable

## Re: Average from specific field

Thanks yet again Stefan.  Sorry for the delay in getting back, I was testing it on my excel spreadsheet, and forgot that excel doesnt seem to like anything that goes over 24 hours.

It has worked perfectly though.

MVP

## Re: Average from specific field

To get intervals correctly displayed in Excel, use a user defined format like

:mm:ss

Regards,

Stefan