Calculating AVG with condition

[fab_castanheira]

Hello everyone,

I have the following (simplified) table :

DAY	EMPLOYEE	NB_CALLS
01/01/2017	John	19
02/01/2017	John	8
03/01/2017	John	13
01/01/2017	Jane	21
02/01/2017	Jane	15
03/01/2017	Jane	10
04/01/2017	Jane	11
...	...	...

The business requirement is to have an expression that calculates the average number of calls per day and employee (no problem) but with the following condition : a day must only be counted if the number of calls is strictly superior to 10.

So the 2nd and 6th line of the table should be "ignored", giving us an average of :

(19+13)/2 = 16 for John

(21+15+11)/3 = 15.7 for Jane

Do you think that this is possible to calculate in an expression, so that the data model doesn't need to be changed ? I have made some tests with the aggr() function but I can't get the correct results...

Thank you for your help, don't hesitate to ask if you need more details about my issue.

[wallerjc]

Could you try something like this?

=Avg({$<NB_Calls-={"<10"}>}NB_Calls)

[sunny_talwar · Accepted solution]

Is NB_CALLS a field name or is this an expression? If this is an expression, then may be this

Avg(Aggr(If(Sum(Calls) > 10, Sum(Calls)), DAY, EMPLOYEE))

[fab_castanheira]

Thank you Sunny for answer, the expression works fine !