3 Replies Latest reply: Jul 20, 2017 5:56 AM by Fabio Castanheira

# Calculating AVG with condition

Hello everyone,

I have the following (simplified) table :

DAYEMPLOYEE

NB_CALLS

01/01/2017John19
02/01/2017John8
03/01/2017John13
01/01/2017Jane21
02/01/2017Jane15
03/01/2017Jane10
04/01/2017Jane11
.........

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.

• ###### Re: Calculating AVG with condition

Could you try something like this?

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

• ###### Re: Calculating AVG with condition

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))

• ###### Re: Calculating AVG with condition

Thank you Sunny for answer, the expression works fine !