Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank with an Avg column (Or only show first 5 records)

Hi,

I am attempting to only show the top 5 / worse 5 using an average.

I have sorted the Pivot Table, with the expression:

((Avg(Price)-DefaultPrice)/DefaultPrice)

This is also the definition of a column in the table.

But when I attempt to use the Rank function:

RANK(((Avg(Price)-DefaultPrice)/DefaultPrice))

all values are 1.

How can I restrict the table to only show the first 5 records, when using the order expression above?

I am using v10

1 Solution

Accepted Solutions
Not applicable
Author

Ended up ordering in SQL and returning only the top 5 results.

A bit pants that there appears to be no other way of doing it.

View solution in original post

13 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Does your expression work if you add in the other parameters for Rank?

RANK(((Avg(Price)-DefaultPrice)/DefaultPrice),0,1)

As for the dimension limits, you need to combine the use of Aggr and Rank.  Take this basic example:

In a chart, the dimension is Customer and the expressions are Sum(Sales) and Rank(Sum(Sales),0,1).  You coulr sort Customer by Rank(Sum(Sales),0,1) without a problem.

If you wanted to just show the top 5 customers in terms of total sales then your dimension would be:

=IF(Aggr(Rank(Sum(Sales),0,1),Customer)<6,Customer)

You would also need to check the "Suppress when value is null" checkbox.

Hope this helps,

Jason

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this

=If(RANK(((Avg(Price)-DefaultPrice)/DefaultPrice), 4) <= 5, RANK(((Avg(Price)-DefaultPrice)/DefaultPrice), 4), Null())

The above expression will calculate for first 5, and then select Suppress Null Value in Dimension/Presentation tabs.

If possible attach sample file.  Hope this helps you.

Regards,

Jagan.

Not applicable
Author

When I do:

RANK(((Avg(Price)-DefaultPrice)/DefaultPrice),0,1)

As an expression in the table, all rows calculate as 1.......

I get the order I want with:

Rank(TOTAL((Avg(Price)-DefaultPrice)/DefaultPrice))

Which presumably I can then do:

=IF(Aggr(Rank(Sum(Sales),0,1),Customer)<6,Customer)

To get the only the top 5

Will confirm when I have done it.....

Thanks

p.s. Would be nice if there were examples for all the expressions and help on how to use them.

Not applicable
Author

Not sure if I will have much hair left after this one.

The expressions works well on the expressions tab:

IF(Rank(TOTAL((Avg(Price)-DefaultPrice)/DefaultPrice)) <11, Rank(TOTAL((Avg(Price)-DefaultPrice)/DefaultPrice)), null())

But you cannot suppress rows on the expressions tab.

When I place the expression in the Dimensions tabs as a Calculated Dimension, I just get the error:

//Error in calculated dimension

What I am doing wrong now? 😞

Jason_Michaelides
Luminary Alumni
Luminary Alumni

I'm sure it's a simple mistake!  Can you post you app please and we can take a look?

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     What are your dimensions?

     Can you tell me in terms of top 5 countries or something like that?

Celambarasan

Not applicable
Author

Currently my dimensions are:

  • Part Number
  • Product Name

The underlying table structure, joins to another table that shows what prices people are selling the product for.

Not sure if this helps.

Not applicable
Author

Will try and provide an example. Not so easy to provide the app itself - containts some sensitive data.

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Then do you need Top 5 Products in each Part Number?or Top 5 Part numbers?

Celambarasan