5 Replies Latest reply: Oct 10, 2011 9:04 AM by Aissam Boumejjane RSS

    How to find top 5 and bottom 5 records in chart

      i am new to QV,

      i have requirement to show top 5 & Bottom 5 spendValue regionwise. I knw to represent top 5 or Bottom 5 values in chart. But i have requiremnt to represent top&bottom5 records @ a time. is there any way to represent.......please help me



      Bala prasad

        • Re: How to find top 5 and bottom 5 records in chart
          Stefan Wühl

          Hi Bala prasad,


          maybe like attached?


          I used a condition in the expression to filter the top 5 and bottom 5 values using rank function, setting every other expression value for a dimension value to zero (or to an extrema your data won't reach).


          I then needed to sort the values descending (because supress zero values won't work with rank, I believe) and set the max visible data points to 10 in presentation tab.


          I think, depending on your requirements, this could work.




            • Re: How to find top 5 and bottom 5 records in chart
              Aissam Boumejjane

              Hi Stefan,


              As a qlikview newbie i'm trying to understand the expression you wrote. I understand the first part .  As for the second part your wrote: rank(-sum(Value),4)<=5


              why did you write "or rank(-sum(Value),4)<=5, sum(Value),0)"


              if(rank(-sum(Value),4)<=5 or rank(-sum(Value),4)<=5) << this is a wrong formula. I'm just experimenting. Why can't this be a working formule?


              How do you manage ")" (brackets) in qlikview? I always find it difficult to know when to "close" a expression with a ")" bracket.


              How you can help me. Cheers!



                • Re: How to find top 5 and bottom 5 records in chart
                  Stefan Wühl

                  Hi aboumejjane,


                  most brackets are required by syntax, e.g. each function like if(), rank(), sum() needs input parameter, those will be given inside the opening and closing brackets. Please refer to the Help for the syntax of QV functions.

                  One may add some brackets to numerical or logical expressions to indicate evaluation priority, but as a golden rule, for every opening bracket there has to be a closing one (and don't close before opening...)


                  Does this answer your third question?


                  I have used this expression:

                  =if(rank(sum(Value),4) <=5 or rank(-sum(Value),4) <=5, sum(Value),0)


                  which is a if() function in principle, where the Help states this syntax:

                  if( condition, then, else )


                  So, the condition is

                  rank(sum(Value),4) <=5 or rank(-sum(Value),4) <=5


                  (rank is a function using a sum(Value) for ranking in this case, the function returns a numerical order) , so the condition should be fulfilled for the top 5 and bottom 5  ranked rows (bottom because I rank for negative values).


                  If the condition is fulfilled, then I want to see sum(Value) in the tables row, else zero.


                  Does this answer your first question?


                  Your expression if(rank(-sum(Value),4)<=5 or rank(-sum(Value),4)<=5 ) uses a valid condition (though the two operands to or operator are identical, aren't they?), but your expression misses a then part, so the total expression as an if() function is not valid (you need to tell QlikView what to do if the condition is fulfilled).


                  Does this answer your second question?


                  @Bala Prasad,

                  I think Anands solution shows a nice way of doing the ranking without the need to limit the max visible data points in presentation tab.


                  So you could combine both presented solutions, if you want to show top 5 and bottom 5 in the same column. Please see attached.

                  (left my old version, right charts the combined version).




              • Re: How to find top 5 and bottom 5 records in chart
                Anand Chouhan




                See the attached sample may be some thing like this