Count Average of Agents per Day

[MichalSa]

Hi!

I'm trying  to count the average distinct agents per day in a call center.

* On Day 1 - there are 5 agents (A,B,C,D,E) who answered 20 calls

* On Day 2 there are 5 agents (A,B,C,D,F)  who answered 20 calls

For a single day I counted the distinct agents (otherwise the count will return 20) but when I want to do an average between more then 1 day - that's when I'm confused - (the correct result I'm expecting is 5)

Selecting the two dates in the filter - results in 6 agents so not correct.

I'm looking to somehow aggregate the distinct count per day and then dividing by distinct dates.

Would appreciate any assistance! - Thanks!

[Vegar · Accepted solution]

Try something like this

Avg(aggr(count (distinct AgentID), DateField))

[MichalSa]

Thanks for the reply!

I tried your solution but the result is still 6 agents which isn't correct.

In excel I would do a combination of  SUMPRODUCT and COUNT - but not sure how to convert it to Sense

[Vegar]

Take a look at the attached qvf file.

[MichalSa]

Must have missed an ')' as now it works!

Thanks!!