# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
Contributor II

## using avg in set analysis

Hello,

I have a table containing training scores of employees, in a range of 0 to 4.

I would like to count the number of employees scoring an average higher than 3.

Sounds ridiculously simple, but I do not seem to make it work.

I tried count( { \$ < avg(score) = {">=3"} > distinct name }, but that does not work.

1 Solution

Accepted Solutions
MVP

You can try this

``````Sum(Aggr(
If(Avg(Score) >= 3, 1, 0)
, name, profile))``````
8 Replies
Contributor III

Hello Frederik,

set expression must have a filter on a field, you can not use expressions to filter.

I think the expression below solves your problem.

count({<Employee={"=avg(Score)>=3"}>} DISTINCT Employee)

Contributor II
Author

Hello Serhan,

unfortunately I get an empty list now...

Partner - Master III

Hello,
@SerhanKaraer  's answer should works for you.
please check if you are using the right field names in the expression.

Contributor II
Author

hello,

you are completely right, there was a typo in my formula.

Now it works.

There is one complication, however.

The training tasks are grouped in competence profiles, each consisting of a number of tasks.
The employees are grouped in teams.

The pivot table has the teams in the rows, the profiles in the columns, and should have the number of employees in a team, scoring on average above 3 on all tasks within the profile, in the cells.

Now it seems the formula you have provided, does not take into account the columns...

Partner - Master III

currently which are your pivot table dimensions and measures?
what is the actual result and the expected one?

Contributor II
Author

Hello,

here is a simplified example:

in the table right bottom I would expect 1 and 2 instead of 3 and 3 ...

MVP

You can try this

``````Sum(Aggr(
If(Avg(Score) >= 3, 1, 0)
, name, profile))``````
Contributor II
Author

yay!