2 Replies Latest reply: Jan 22, 2014 4:01 PM by Massimo Grossi RSS

    Help with aggr/Rank functions



      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!



        • Re: Help with aggr/Rank functions
          Jeremiah Kurpat

          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
            Massimo Grossi

            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)