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
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.
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
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.
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 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? 😞
I'm sure it's a simple mistake! Can you post you app please and we can take a look?
Hi,
What are your dimensions?
Can you tell me in terms of top 5 countries or something like that?
Celambarasan
Currently my dimensions are:
The underlying table structure, joins to another table that shows what prices people are selling the product for.
Not sure if this helps.
Will try and provide an example. Not so easy to provide the app itself - containts some sensitive data.
Hi,
Then do you need Top 5 Products in each Part Number?or Top 5 Part numbers?
Celambarasan