Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

fab_castanheira
New Contributor II

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.

1 Solution

Accepted Solutions

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

3 Replies
wallerjc
New Contributor III

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

fab_castanheira
New Contributor II

Re: Calculating AVG with condition

Thank you Sunny for answer, the expression works fine !

Community Browser