Qlik Community

New to Qlik Sense

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

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
frederikverhaeghe
Contributor II
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. 

I am using enterprise, with no access to the load script...

Hoping for your inspiration!

1 Solution

Accepted Solutions
sunny_talwar

You can try this

Sum(Aggr(
    If(Avg(Score) >= 3, 1, 0)
, name, profile))

View solution in original post

8 Replies
SerhanKaraer
Contributor III
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)

frederikverhaeghe
Contributor II
Contributor II
Author

Hello Serhan,

unfortunately I get an empty list now...

 

agigliotti
Partner - Master III
Partner - Master III

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

frederikverhaeghe
Contributor II
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...

agigliotti
Partner - Master III
Partner - Master III

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

frederikverhaeghe
Contributor II
Contributor II
Author

Hello,

here is a simplified example:

frederikverhaeghe_0-1591625767332.png

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

sunny_talwar

You can try this

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

yay!

 

frederikverhaeghe_0-1591627410234.png