Qlik Community

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

Re: Average from specific field

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

:mm:ss

Regards,

Stefan

Community Browser