topic Re: Help with aggr/Rank functions in QlikView

Help with aggr/Rank functions

Wed, 22 Jan 2014 12:00:56 GMT

Hi,

I'm relatively new to QV and even newer to this forum so please bear with me if I'm asking a stupid question/have got it completely wrong!

I'm working on an tool to analyse examination performance by capturing the question responses given by candidates. I've become stuck when I try and calculate question pass rates for the top third and bottom third of candidates by total score awarded. Calculating the overall pass rate is simply sum(PointsAwarded)/count(Result) which returns a percentage - I'm trying to do the same thing but on the top and bottom 33 percentiles by overall score.

I've so far tried *many* variations of...

(aggr(if(Rank(TotalPointsAwarded)<=(count(Result)/3), PointsAwarded), QuestionNo))/(count(Result)/3)

I've tried using this as a calculated dimension, expression and conditional expression in, both, pivot and straight tables. I've also tried using the 'fractile' function but can't get either to return anything meaningful. Even when I try just creating a table to return the top ie 5 scores I can't get a simplified version of the above to work.

Any and all help would be gratefully received!

Thanks

Re: Help with aggr/Rank functions

jerem1234 — Wed, 22 Jan 2014 15:43:56 GMT

How about selecting all the candidates that are in the top third/bottom third instead of doing it in chart?

I tried guessing exactly what you are trying to do but there is no Result field in your excel file. I assumed since it is question pass rate, it is the amount of people that answered that question (number of candidates). I used a dimension of QuestionNo and expression:

sum(PointsAwarded)/count(distinct RegistrationNo)

But what I added are two text boxes that will select the top third and bottom third candidates based on total score.

I added an action so when you click, it selects those candidates. I used the formulas using a Select in Field action:

='(' & concat({1}if(aggr(rank(sum({1}PointsAwarded), 4, 1), RegistrationNo) <= count({1}total distinct RegistrationNo)/3, RegistrationNo), '|') & ')'

='(' & concat({1}if(aggr(rank(sum({1}PointsAwarded), 4, 1), RegistrationNo) >= count({1}total distinct RegistrationNo)*2/3, RegistrationNo), '|') & ')'

If this doesn't accomplish what you are looking for, can you describe in more detail and maybe post a sample application that demonstrates your problem?

Please find attached

Hope this helps!

Re: Help with aggr/Rank functions

maxgro — Wed, 22 Jan 2014 21:01:30 GMT

Did you try to read this post?

Re: calcul moyenne sur certains enregistrements

=Avg({<TotalPointsAwarded={"<=$(=Fractile(TotalPointsAwarded,33/100))"}> +

<TotalPointsAwarded={">=$(=Fractile(TotalPointsAwarded,66/100))"}>} TotalPointsAwarded)