Top 5 and Bottom 5 values in Straight Table

[Anonymous]

I created a Straight Table with columns

Products     2013     2014     Increse/Decrease

Increase or decrease is the difference between the Sales in 2014 and 2013. Based on this value, I should calculate Top 5 and Bottom 5. However, I can find only Dimension Limits. Is there any alternative? I found Rank as one. Not sure how to use it.

[tresesco]

Yes rank() is an alternative. Since, you may have already gone through some threads here with not much luck, it would be better if you can provide your sample qvw where we can show 'how' .

[Anonymous]

Hi,

Except Product, other 3 fields are expression. So, based on the Increase or Decrease expression, I need to display the top 5 values as well as bottom 5 values (Separately)

Dimension Limits accepts only first Expression.

[malini_qlikview]

If you have calculated Increase/decrease then try using

=If(Rank([Increase/Decrease])<=5,'Top 5','Bottom 5') in another column to find top and bottom values

[Peter_Cammaert]

Put the Increase/dDecrease value in front of the other expressions and your Dimension limits will be usable again.

To get top5 and bottom 5 in a "seemingly" single table, create two straight tables, one with the top 5 and one with the bottom 5 (again using dimension limits), cut the head of the second table and glue them together.

I don't think there is a simpler method to accomplish this.

[maniram23]

Hi,

Try this. it is help full for you

i have two fields like  Revenue And Customer.

=aggr(if(rank(sum(Revenue))<=10 or rank(-sum(Revenue))<=10,Customer),Customer).

[Clever_Anjos]

Maybe this Top 5 &amp; Bottom 5 Values can help you

[Anonymous]

Hi Peter,

Sorry for confusing you. I intended to do Top 5 and Bottom 5 in separate Chart.

Also, I don't want to keep Growth as the first expression.

Basically except Product, other 3 are expressions.

2013 Field - Has some Expression

2014 Field - Has some Expression

Increase/Decrease Field - (2014 Expression) - (2013 Expression)

I need to show the Top 5 values based on Growth field and Bottom 5 values in separate tables.

Hope I explained it clearly.

[vikasmahajan · Accepted solution]

PFA

Vikas

[#10]

use Calculated Dimension :

=aggr(if(rank(sum(FACT))<=5 or rank(-sum(FACT))<=5,DIMENSION),DIMENSION)