topic Re: Top 5 and Bottom 5 values in Straight Table in QlikView

Top 5 and Bottom 5 values in Straight Table

Anonymous — Tue, 06 Oct 2015 12:07:40 GMT

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.

Re: Top 5 and Bottom 5 values in Straight Table

tresB — Tue, 06 Oct 2015 12:36:18 GMT

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

Re: Top 5 and Bottom 5 values in Straight Table

Anonymous — Tue, 06 Oct 2015 12:55:13 GMT

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.

Re: Top 5 and Bottom 5 values in Straight Table

malini_qlikview — Tue, 06 Oct 2015 16:02:13 GMT

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

Re: Top 5 and Bottom 5 values in Straight Table

Peter_Cammaert — Tue, 06 Oct 2015 16:33:03 GMT

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.

Re: Top 5 and Bottom 5 values in Straight Table

maniram23 — Tue, 06 Oct 2015 17:51:49 GMT

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

Re: Top 5 and Bottom 5 values in Straight Table

Clever_Anjos — Tue, 06 Oct 2015 17:54:11 GMT

Maybe this Top 5 & Bottom 5 Values can help you

Re: Top 5 and Bottom 5 values in Straight Table

Anonymous — Wed, 07 Oct 2015 04:52:28 GMT

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.

Re: Top 5 and Bottom 5 values in Straight Table

vikasmahajan — Wed, 07 Oct 2015 05:03:40 GMT

PFA

Vikas

Re: Top 5 and Bottom 5 values in Straight Table

Wed, 07 Oct 2015 05:06:49 GMT

use Calculated Dimension :

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

Re: Top 5 and Bottom 5 values in Straight Table

Wed, 07 Oct 2015 05:07:35 GMT

Refer JohnW‌ solution at TOP 10 and Bottom 10 Customer by Revenue Amount