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.
top5bottom5.qvw 167.2 K
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!
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?
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).
top5bottom5_2.qvw 161.8 K