topic Re: Average from specific field in QlikView

Average from specific field

Thu, 30 Aug 2012 11:20:29 GMT

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?

Re: Average from specific field

swuehl — Thu, 30 Aug 2012 11:28:50 GMT

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.

Re: Average from specific field

Thu, 30 Aug 2012 11:39:17 GMT

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.

Re: Average from specific field

swuehl — Thu, 30 Aug 2012 11:44:27 GMT

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

Re: Average from specific field

Thu, 30 Aug 2012 11:52:16 GMT

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

Re: Average from specific field

swuehl — Thu, 30 Aug 2012 12:04:53 GMT

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

)

Re: Average from specific field

Thu, 30 Aug 2012 13:57:05 GMT

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.

Re: Average from specific field

swuehl — Thu, 30 Aug 2012 14:04:46 GMT

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

:mm:ss

Regards,

Stefan