Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Top 5 and Bottom 5 values in Straight Table

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.

1 Solution

Accepted Solutions
Highlighted
Champion
Champion

10 Replies
Highlighted
MVP
MVP

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' .

Highlighted
Creator III
Creator III

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.

Highlighted
Creator II
Creator II

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

Untitled.png

Highlighted

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.

Highlighted
Creator II
Creator II

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).

Highlighted
Employee
Employee

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

Highlighted
Creator III
Creator III

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.

Highlighted
Champion
Champion

Highlighted
Not applicable

use Calculated Dimension :

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