Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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

)

)

View solution in original post

7 Replies
swuehl
MVP
MVP

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
Author

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
MVP
MVP

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

Not applicable
Author

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
MVP
MVP

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
Author

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
MVP
MVP

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

:mm:ss

Regards,

Stefan