Average from specific field

[#1]

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?

[swuehl]

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.

[#3]

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. 

[swuehl]

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

[#5]

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

[swuehl · Accepted solution]

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]

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.

[swuehl]

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

:mm:ss

Regards,

Stefan