Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
When I use the expression rank(score) I get the correct results.
Now I want to count all employees whose highest score=100 and in another expression the employees whose highest score = 50
An employee can have multiple records with scores
my expression : = count(distinct if (score = 100 and aggr(rank(score),employee)= 1, employee))
must be something wrong because i don't get any results.
Is it possible to use a rank function in a count function ?
Hi,
Why not using set analysis like this?
For highest score=100:
=count({$<[highest score] = '100'>} distinct employee)
For highest score=50:
=count({$<[highest score] = '50'>} distinct employee)
Because I am still struggling with set analysis .
In your example the ranking is missing. So a score of 50 should not be counted when the employee has a score of 100 too.
Try to post some data and explain what you really want, is better for the community in order to help you.
Set v100 to 100 and v50 to 50 in Variable Expression and do this:
For highest score <= 100:
=count({$<[highest score] = {'<=$(=v100)'}>} distinct employee)
For highest score <= 50:
=count({$<[highest score] = {'<=$(=v50)'}>} distinct employee)
This will do the trick
your solutions may be correct, but still doesn't answer my question whether it is possible to combine the distinct count whith a rank function.
Suppose there a differences in the highest score, and I want to present the count of the highest scores in a chart.