Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
And the following as expressions:
Hope that makes sense.
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.
There will certainly be another way to do it. If you could post your app it would help us provide a solution for you.
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.