Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)
or
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
Aggr(Sum(Cost), Inspection) / Count(distinct Inspection)
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.