topic Re: Calculate Averages in set analysis in QlikView

Calculate Averages in set analysis

Thu, 01 Dec 2016 08:11:38 GMT

Good morning,

I am trying to calculate averages for the attached file. The formula I have used to calculate

Total_PreviewTime=Sum ({<PreviewedFirst={1}>}PreviewTime) and I want to calculate the Average Total_PreviewTime and similarly for

Total_CallTime=sum(CallTime) I want to calculate Average Total_CallTime

PFA,

Thanks

Re: Calculate Averages in set analysis

MK_QSL — Thu, 01 Dec 2016 08:57:58 GMT

What about

Avg({<PreviewedFirst={1}>}PreviewTime)

and

Avg({<Total_CallTime={1}>}Total_CallTime)

Re: Calculate Averages in set analysis

Thu, 01 Dec 2016 09:03:39 GMT

Hi Manish,

I am actually looking for the Average of Sum ({<PreviewedFirst={1}>}PreviewTime), if there is something like

avg(Sum ({<PreviewedFirst={1}>}PreviewTime)). Not sure if this is possible

Thanks

Re: Calculate Averages in set analysis

tresB — Thu, 01 Dec 2016 09:06:42 GMT

You can try something like:

avg(Aggr(Sum ({<PreviewedFirst={1}>}PreviewTime), Dim1))

Where Dim1 is dimension(s) on which the aggregation is desired.

Re: Calculate Averages in set analysis

sunny_talwar — Thu, 01 Dec 2016 09:07:01 GMT

May be something like this:

Avg(Aggr(Sum({<PreviewedFirst={1}>}PreviewTime), CallCentre, Desk, Team_Lead, Agent_Name, Day))

Re: Calculate Averages in set analysis

MK_QSL — Thu, 01 Dec 2016 09:07:42 GMT

You need to use Aggr as per Tresesco;s reply..

Avg(Aggr(SUM(YourExpression),Dim1,Dim2,Dim3))

Re: Calculate Averages in set analysis

Thu, 01 Dec 2016 09:33:08 GMT

Thank you for the all the responses, when I use this =Avg(aggr(Sum ({<PreviewedFirst={1}>}PreviewTime),Day)) and I use Interval format (hh:mm:ss) and my graph comes like

Re: Calculate Averages in set analysis

sunny_talwar — Thu, 01 Dec 2016 09:35:53 GMT

So this is not what you are looking to do? What is your expected output here?

Re: Calculate Averages in set analysis

tresB — Thu, 01 Dec 2016 09:50:49 GMT

You probably need same average across days, if so try like:

=Avg(Total aggr(Sum ({<PreviewedFirst={1}>}PreviewTime),Day))

Re: Calculate Averages in set analysis

Thu, 01 Dec 2016 09:54:46 GMT

Hi Sunny T,

The formula is fine but now when I put on the line graph, the I select the format of Interval hh:mm:ss is not translating correctly. I am expecting the time intervals of 00:00:00 to 00:30:00

thanks

Re: Calculate Averages in set analysis

tresB — Thu, 01 Dec 2016 10:21:24 GMT

Perhaps the output you are getting has to be parsed as seconds. Try like:

Time#(Avg(aggr(Sum ({<PreviewedFirst={1}>}PreviewTime),Day)), 'ss')

and then apply your time format in the presentation tab.

Re: Calculate Averages in set analysis

Anonymous — Thu, 01 Dec 2016 10:30:30 GMT

Hi Moses,

you can try

Aggr(Sum ({<PreviewedFirst={1}>}PreviewTime),Dim1,Dim2,Dim3) / Count ({<PreviewedFirst={1}>}PreviewTime)

Re: Calculate Averages in set analysis

Thu, 01 Dec 2016 10:41:22 GMT

Thank you much for your assistance and but Payal's is correct and the format is also correct.