apps-infra

    Forum Stats

  • 3,873,748 Users
  • 2,266,637 Discussions
  • 7,911,626 Comments

Discussions

Top N and Bottom N reports

Hi

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.
Tagged:

Answers

  • David_T
    David_T Member Posts: 2,432
    There is a very simple way to to this with a Table without using the Rank function at all. Simply do this: in the Properties of Table View, restrict the number of visible rows to 10. Then, in the same window, click on the "enable column sorting" checkbox. Set the sorting on the measure column to descending.

    Go to your dashboard. When the report comes up, it is already showing the top 10. Now click in the header of the measure to switch the sorting. Voila, because the table only show 10 rows, you instantly have the bottom 10. The beauty of this also is that if the end user wants to see everything after all this, he/she can simply click the button at the bottom to show all records.
    David_T
  • 704715
    704715 Member Posts: 134
    Hi,

    Add one more column to report with column formula as RSUM(1) and add filter on this column some think like RSUM(1) <=10 , rename column name rsum(1) to Sequence NO or Rank.

    --Srinivas Malyala
    704715
  • 663424
    663424 Member Posts: 37
    Hi David

    Thanks for the suggestion. The thing is, the user wants to see only the Top/Bottom 10 customers.
    Is it possible to restrict the number of rows displayed to 10 and not give him the option of seeing all the records.

    Thanks.
  • 663424
    663424 Member Posts: 37
    Hi Srinivas

    I'll try this out. Do you mean for the bottom 10 customers report, I can put the Utilisation in ascending order and use RSUM and For the Top 10
    put the Utilisation in descending order and then use RSUM ?

    Thanks a lot.
  • 704715
    704715 Member Posts: 134
    yes,

    your current top10 report add one more column RSUM and one more filter to that report, same with Bottom10 report.

    --Thanks
    Srinivas
    704715
  • Kranthi.K
    Kranthi.K Member Posts: 1,586
    Hi,

    All the suggestion above are right,seems your new so i would give you an example given by expert,for that go through this link

    [http://obiee101.blogspot.com/search/label/TOPN]

    Hope it helps you.

    Best Wishes,
    Kranthi.
  • 663424
    663424 Member Posts: 37
    Hi Kranthi

    Thanks for the link. I actually went through the page before posting, but somehow the rank concept did not quite fit in
    with the kind of data I had (419 having one rank and 3 having different ranks).

    Thanks
This discussion has been closed.
apps-infra