How to calculate an average over other averages

[Anonymous]

HI,

I have the following pivot table, which shows per person, per weekday and per hour the number of calls over a certain periode of time.

I calculate the average number of calls for this person per weekday per hour ("Average #calls") like this:

= count(DISTINCT OproepId)/count(DISTINCT OproepDatum)

OproepId = Unique id for the call

Oproepdatum= Date of the call

Now I want to calculate the average over the "average #calls" so I can compare the average of a person per weekday and hour to the average over all persons per weekday, per hour.

I tried already something like:

sum( aggr(NODISTINCT count(DISTINCT OproepId)/count(DISTINCT OproepDatum), OproepGestartDoor, DagWeek, Uur))

But no success until now

How to do this in the correct way?

[sunny_talwar]

May be this

Avg(TOTAL Aggr(Count(DISTINCT OproepId)/Count(DISTINCT OproepDatum), OproepGestartDoor, DagWeek, Uur))

[Anonymous]

Thanks Sunny for your reply, but this gives the following result:

This is probably an average over all averages and I want an average over the averages per weekday per hour for all persons.

So, Saterday 14 hour has avarages of 3,00 and 1,00 and should bee 2,00. Saterday 15 hour has avarages of 5,00 and 1,00 so should be 3,00. And so on...

[sunny_talwar · Accepted solution]

Try this

Avg(TOTAL <Weekday, Hour> Aggr(Count(DISTINCT OproepId)/Count(DISTINCT OproepDatum), OproepGestartDoor, DagWeek, Uur))

[Anonymous]

Super! This seems to be what I want. Thanks very much Sunny.