Top N and Bottom N reports
663424Sep 7 2009 — edited Sep 8 2009Hi
I need to create two reports 'Top 10 customers' and 'Bottom 10 customers' based on a measure 'Utilisation'
say..
Cust Number Cust Name Utilisation
My data is such that I have 422 records, of which 419 have a utilisation of 50% and 3 records have 0%.
I created a new column in Answers with the formula : rank(Utilisation.Utilisation desc) and filtered it saying 'Rank is less than or equal to 10'...
So what happens is the 419 records with 50% utilisation get rank 1 and the remaining 3 records get 420, 421 and 422. This happens irrespective
of whether I put rank(Utilisation.Utilisation desc) or rank(Utilisation.Utilisation asc).
So to get the Top 10 customers 'Rank is less than or equal to 10' condition gets me all 419 records when I need only 10 records. This obviously happens because
all the 419 records have rank 1. Is there a way I can restrict the records. Say if I have
Customer Utilisation
A 10
B 20
C 30
D 40
E 40
F 40
G 40
and I want to see Top 4, I should see only A, B,C and D. Maybe if the Utilisation is equal order by Name in ascending.
Also, can anyone help me on how to get the Bottom 10 customers.
Thanks a lot.