topic Re: Average of sum in QlikView

Average of sum

Thu, 13 Jun 2013 09:55:43 GMT

I am looking for one way to get an average of sum. I tried avg(sum(fieldname)) and in return I got only 0.

In my example I got car inspeccions done and I want to know in average how much I do by hour for instance.

My data is loaded inspeccion by inspeccion and has got all the information concerning the exact time it is done.

thanks in advance for the help,

afonso

Re: Average of sum

jfkinspari — Thu, 13 Jun 2013 10:07:05 GMT

You could use the aggr function. Let's say you want to calc the avg of some sum per hour that would be

avg(aggr(sum(Fieldname),HourFieldname))

The aggr function gives you a list of values calculated by HourFieldname, which you then calculates the avg of.

Re: Average of sum

hic — Thu, 13 Jun 2013 10:09:01 GMT

This is a nested aggregation. You first want to sum the costs per inspection. Then you want to calculate some average. For instance the average cost per inspection would be

Sum(Cost)/Count(distinct Inspection)

Avg(Aggr(Sum(Cost), Inspection))

How you want the number of hours to influence the calculation I am not sure. Perhaps

Sum(Cost)/Sum(Hours)

and use Inspection as dimension?

HIC

Re: Average of sum

Thu, 13 Jun 2013 10:20:55 GMT

Aggr(Sum(Cost), Inspection) / Count(distinct Inspection)

Re: Average of sum

Thu, 13 Jun 2013 14:52:26 GMT

thanks for your tip.

I tried using aggr function but it didn't work as I was expecting. I end up dividing sum (receptions) by count (distinct calendardate) and it work perfectly.