Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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

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

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.

13 Replies
Partner
Partner

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

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

MVP & Luminary
MVP & Luminary

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

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

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

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

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

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? 😞

Partner
Partner

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

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

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

Hi,

     What are your dimensions?

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

Celambarasan

Not applicable

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

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

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

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

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

Hi,

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

Celambarasan