13 Replies Latest reply: Apr 26, 2012 10:51 AM by Richard Norris

# 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

• ###### 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

• ###### 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.

• ###### 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? :-(

• ###### 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)

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,

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.

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

Hi,

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

Celambarasan

• ###### 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.

• ###### 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

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

If I have the data:

 PartNo ProductName Reseller DefaultPrice Price Product 1 Product 1 Reseller1 £65.00 £76.40 Product 1 Product 1 Reseller2 £65.00 £84.77 Product 1 Product 1 Reseller3 £65.00 £124.76 Product 1 Product 1 Reseller9 £65.00 £57.91 Product 2 Product 2 Reseller8 £82.50 £150.54 Product 2 Product 2 Reseller2 £82.50 £107.14 Product 2 Product 2 Reseller3 £82.50 £113.08 Product 3 Product 3 Reseller1 £165.00 £177.11 Product 3 Product 3 Reseller8 £165.00 £179.99 Product 3 Product 3 Reseller2 £165.00 £214.41 Product 3 Product 3 Reseller3 £165.00 £304.68

I only want to see the products where the expression:

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

Is less than 6

So at the moment in the Pivot Table, I have the following Dimensions

• Part No,
• Product Name
• Default Price

And the following as expressions:

• ((Avg(Price)-DefaultPrice)/DefaultPrice)
• Avg(Price)
• PriceIncVat-Avg(Price)

Hope that makes sense.

• ###### 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.

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

There will certainly be another way to do it.  If you could post your app it would help us provide a solution for you.

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

The way I've done it is just to work out what the average is, order by rank and then on the presentation tab, check 'Max Number' and set it to ten. (You could set it to five.) Likewise for 5 worst, you would just do a sort on the rank ascending instead of descending.