Top N and Others

[Former Member]

Hello,

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.

[Former Member]

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!

Regards

Anne

[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.

Steps:

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.

Disadvantages:

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,

SV

[Former Member]

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.

Regards.

Anne

[arvind_ramachandran2]

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.

Regards

Arvind R

[Former Member]

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

Ashish..

[Former Member]

Hello Ashish,

Thanks for your response. We'll try that, and will let you know how it goes.

Best regards,

Pat M.

[Former Member]

Pat,

Did the concept work for you. We have the same need to create a top n then have all others in one bucket.

Please let me know your results.

Thanks,

Muke

mukenaby@hotmail.lcom

[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,

RR