topic Re: Calculating AVG with condition in App Development

Calculating AVG with condition

fab_castanheira — Wed, 19 Jul 2017 13:18:01 GMT

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.

Re: Calculating AVG with condition

wallerjc — Wed, 19 Jul 2017 16:00:46 GMT

Could you try something like this?

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

Re: Calculating AVG with condition

sunny_talwar — Wed, 19 Jul 2017 16:22:15 GMT

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

fab_castanheira — Thu, 20 Jul 2017 09:56:49 GMT

Thank you Sunny for answer, the expression works fine !