Skip to Content
author's profile photo Former Member
Former Member

Top N and Others


We're trying to add an "others" total to a Top N query, and would appreciate some help with resolving it. There are a few similar topics to this in the forum, but I cannot see one with a solution.

Thanks in advance,

Pat M.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 20, 2005 at 03:33 PM

    Hi Pat,

    Never tried this..Just an idea..

    You could have a structure where you have the characteristic in first selection with a replacement path variable ..where the variable is populated from another top N query..

    and the second selection which could be a formula like Total - Top N ..which will give you the total for others..

    Hope it works..Let me know..


    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      BI Experts,

      I'm in BI 7.0 and looking for the same requirement from my client. Pls let me know if anyone had any thoughts on this.

      My report consist a structure(curr. mnth, last mth etc) by Customer Group. When you run the query need to display Top 20 and rest to collapse into 'Others' bucket.

      Initially I thought of doing using 2 queries in two different sheets and link the main sheet in a workbook with the result sets. But I'm not able to create the second query which is rest of the top 20.

      I tried sum of the subtotal or Outline functions with the excel functionality and didn't find any luck.

      Quick response is appreciated.

      Best Rgds,


  • author's profile photo Former Member
    Former Member
    Posted on Sep 12, 2008 at 11:45 AM

    Hi Ashish

    I am also interested in this topic because I have the same need:

    - Display the sales volume of my Top N customers

    - Display the sales volume of my complete store

    I wanted to use the solution you have described: create a characteristic value variable with a replacement path. The variable is populated with another query based on a Top N Customer.

    Create in my query two key figures : first one based on sales volume and restricted on my customer variable, second one not restricted.

    The problem is that the BEX does not authorize such variable in a structure / restricted key figure.

    So how did you get a result with the solution described in ?

    Thanks a lot for you help.



    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Anne,

      You can do this using formula mode (I'm assuming that you're using BI 7)

      In the hidden sheet, insert the query that has the condition (Top 20). Do not hide the result row. The condition will not affect the result row and it will show the overall result. So the 'Others' can be calculated using an Excel forumla.

      In the visible sheet, you can present the data in the way you require by refering to the results in the hidden sheet.

      This worked for me, but not sure whether it will address your

      entire scenario.


      Arvind R

  • author's profile photo Former Member
    Former Member
    Posted on Sep 18, 2008 at 11:18 AM

    Hi Arvind

    Thanks a lot for your reply but unfortunately, I m not in BI 7 !!!!

    So this is why I do not get right results with replacement path!

    And in fact I have never seen all the shhet your are speaking about (hidden sheet, visible sheet). This is certainly new from Netweaver 04 . SO I have to wait for a possible migration of our system, or use worbooks!

    Thanks a lot for your help!



    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hey Guys,

      Thanks Arvind. I did exactly same way as you described way back but didn't update to the forum due to busy go-live tasks.

      In our case I was able to convince the client that this feature will work static not dynamic. I mean the solution will work for top 20, 10, 30 only in fixed format, where the variable value is populated with default value 20.


      1. use the same query in 2 sheets where the first sheet is with no totals and second sheet with row totals aligned 'top'.(remember the variable value for Top N is fixed with '20').

      2. Assume that the top 20 results have displayed from D6:D26 rows in first sheet where as the results have displayed in D7:D27 2nd sheet (remember sheet 2 uses results row on top)

      2. So, in first sheet the D27 row contains the 'Others' node hard-coded and the formula is as below:

      =(Sheet!(D6) - SUM(D6:D26) "results for 'Other'

      =Sheet!D6 "to bring the totals from sheet if user wants the total to be displayed in bottom otherwise we don't need this and we can display on top.

      After completion of designing the workbook, then hide sheet 2.


      1. Static in nature(of course somebody good at VBA macros they can make it dynamic)

      2. by any chance if you change the variable value to 10 then the results works fine but there will be 10 blank rows between the Top 10 and the 'Others' row. you got to delete the blank rows(mannual work)

      I was able to convince the user to use workbook for top 20 purpose, where as use the query for any additional top N purpose I mean if they want to display top 50 they can use query separately but there won't be any 'Others' here.

      Awarding some points to Arvind as he posted partial solution.

      Best Rgds,


Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.