BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Ranking in a table or chart for TOP N + OTHERS
2 members found this topic helpful

 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
xmarigo
Forum Member
Forum Member



Joined: 15 Dec 2006

Posts: 29



PostPosted: Wed Jan 28, 2009 12:13 am 
Post subject: Ranking in a table or chart for TOP N + OTHERS

Hi all,

we are facing a challenge using Web Intelligence XIR2 when trying to achieve the following customer requirement:

We have a list like the one below:
A 30
B 15
C 40
D 41
E 20

We need a ranking for the TOP 3 (D,C,A) but we also want to show a grouped row called OTHERS with the amount for the rest of Dimensions which are excluded from the top 3. In this example above, we should need the Table / Pie Chart as Follows:

D 41
C 40
A 30
OTHERS 35

We've tried using context variables and we've been able to find a way to implement this behaviour using 2 queries, one of them having a RANKING in the Data Provider itself.

However, as this quite a tedious way of doing it and, moreover, requirements are a little bit more complex (they want to drill by this dimension and keep the same behavior at a lower level of the hierarchy and so on...) we wonder wether there's an easier way to get this to work properly.

Hope the explanation is clear. Any input is welcome.

Thanks,

Xavi
Back to top
xmarigo
Forum Member
Forum Member



Joined: 15 Dec 2006

Posts: 29



PostPosted: Sun Feb 15, 2009 3:17 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

I already found a workaround for this one. If anybody is interested in getting this to work, I can show you the approach we've used.

Thanks

Xavi
Back to top
Nick Daniels
Forum Aficionado
Forum Aficionado



Joined: 15 Aug 2002

Posts: 14246
Location: England


flag
PostPosted: Sun Feb 15, 2009 7:52 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

We're always interested in tips and tricks. I'm sure there are many members who would like to see the details posted here icon_wink.gif
Back to top
DoSTh
Senior Member
Senior Member



Joined: 31 May 2007

Posts: 82
Location: Bangalore


flag
PostPosted: Mon Feb 16, 2009 4:05 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

What my idea is a trick.I will be happy if it useful to you.

First let us take an example
i want top 4 vendors based on balance.
click on ranking button select top 4

now click on second column that is balance,click on on sum button.
now it will show extra row with sum label.
change the sum label to "Others".


create a variable with following formula.
others=NoFilter(Sum([balance]))-Sum(balance]).
drag this variable instead of sum value column.

This will give you sum of balance for Others.

Hope this will help u.
Any doubt plz reply or mail me.

_________________
DOSTH

Those who joyfully leave everything in God's hand will eventually see God's hand in everything
Back to top
Jamesr60
Senior Member
Senior Member



Joined: 14 Jul 2009

Posts: 40
Location: UK


flag
PostPosted: Tue Jul 21, 2009 4:30 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

Dominic Savio Thomas wrote:
What my idea is a trick.I will be happy if it useful to you.

First let us take an example
i want top 4 vendors based on balance.
click on ranking button select top 4

now click on second column that is balance,click on on sum button.
now it will show extra row with sum label.
change the sum label to "Others".


create a variable with following formula.
others=NoFilter(Sum([balance]))-Sum(balance]).
drag this variable instead of sum value column.

This will give you sum of balance for Others.

Hope this will help u.
Any doubt plz reply or mail me.


Mr Savio Thomas - that is brilliant! Thanks very much...
Back to top
DoSTh
Senior Member
Senior Member



Joined: 31 May 2007

Posts: 82
Location: Bangalore


flag
PostPosted: Tue Jul 21, 2009 4:36 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

Hi James,
So happy to hear that it helped you...

Thanks,
Dominic Savio Thomas

_________________
DOSTH

Those who joyfully leave everything in God's hand will eventually see God's hand in everything
Back to top
1dd
Forum Member
Forum Member



Joined: 11 May 2009

Posts: 6



PostPosted: Wed Aug 05, 2009 10:33 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

That's indeed very smart icon_smile.gif
Back to top
jvmauricio
Principal Member
Principal Member



Joined: 03 Aug 2009
ASUG Icon
Posts: 379
Location: Bagong Silang, Caloocan


flag
PostPosted: Thu Aug 06, 2009 2:43 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

How about in a Pie chart? is it possible? please help...

Thanks

_________________
---------------------------------------------------

�Attempting to force one technology or tool to satisfy a particular need for which another tool
is more effective and efficient is like attempting to drive a screw into a wall with
a hammer when a screwdriver is at hand: the screw may eventually enter the wall but at what cost?�


-Dr Edgar �Ted� Codd (1923-2003)
-The Father of OLAP principles
Back to top
DoSTh
Senior Member
Senior Member



Joined: 31 May 2007

Posts: 82
Location: Bangalore


flag
PostPosted: Thu Aug 06, 2009 8:08 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

Hi jvmauricio,

I tried the following today and found to be working.
There may be easy solutions for this.Then also try this and let me know whether it is working for you or not.

Example.
We have state and sales revenue.
I want to display Top 3 values and Others in pie chart.

create first variable:
var_state:
Code:
If (Rank(<State> ,<Sales revenue>) )=4 Then "Others" Else <State>

Create the second variable
var_new_salesrevenue:
Code:
= If (Rank(<State> ,<Sales revenue>) )=4 Then 0 Else <Sales revenue>

create third variable
var_salesrevenue:
Code:
= If (Rank( <State> ,<Sales revenue>))=4 Then  (NoFilter(Sum(<Sales revenue>))-Sum(<var_new_salesrevenue>)) In   Block Else<Sales revenue>


Our variables are ready now.
drag <state>,<var_state> and <var_salesrevenue> into report.
Now select <state> column and click on the rank button.
Select Top 4(not top 3)
Based on <Sales revenue>

Right click on the table =>Turn to chart
Select pie chart.
Click OK.

Now you will get a blank page.

Select the blank area.You can see the boarders of the chart is selected.
Right click then go to Pivot tab
Now drag var_state to X axis and hide the state.
Click Apply..

I have attached a screen shot of the result.

Thanks,
Dominic Savio Thomas

_________________
DOSTH

Those who joyfully leave everything in God's hand will eventually see God's hand in everything
Back to top
jvmauricio
Principal Member
Principal Member



Joined: 03 Aug 2009
ASUG Icon
Posts: 379
Location: Bagong Silang, Caloocan


flag
PostPosted: Thu Aug 06, 2009 8:52 pm 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

Sir Dominic Savio Thomas

First id like to thank you for the help icon_wink.gif , anyway im having some problems with this part

Quote:
drag <state>,<var_state> and <var_salesrevenue> into report.


When i dragged the var_state variable into the report/column im getting this "#CONTEXT" into each row of the column.

any ideas? BTW im using Webi XI R3.

Thanks and Regards.

_________________
---------------------------------------------------

�Attempting to force one technology or tool to satisfy a particular need for which another tool
is more effective and efficient is like attempting to drive a screw into a wall with
a hammer when a screwdriver is at hand: the screw may eventually enter the wall but at what cost?�


-Dr Edgar �Ted� Codd (1923-2003)
-The Father of OLAP principles
Back to top
jvmauricio
Principal Member
Principal Member



Joined: 03 Aug 2009
ASUG Icon
Posts: 379
Location: Bagong Silang, Caloocan


flag
PostPosted: Thu Aug 06, 2009 10:54 pm 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

Sir Dominic Savio Thomas

I Have found another solution,

First i Created a Dimension "TOP 10 PRODUCTS" with the formula


Quote:
=If(Rank([Sales_total];[Productname]) < 11;[Productname];"OTHERS") In ([Productname])


then created a table with the column:

TOP 10 PRODUCTS-------SALES

It worked perfectly with pie charts and aggregates the sales automaticaly.


Anyway thanks for that solutions of yours it helped me a lot and to others who helped me...

Regards BOB! thumbsup.gif

_________________
---------------------------------------------------

�Attempting to force one technology or tool to satisfy a particular need for which another tool
is more effective and efficient is like attempting to drive a screw into a wall with
a hammer when a screwdriver is at hand: the screw may eventually enter the wall but at what cost?�


-Dr Edgar �Ted� Codd (1923-2003)
-The Father of OLAP principles
Back to top
DoSTh
Senior Member
Senior Member



Joined: 31 May 2007

Posts: 82
Location: Bangalore


flag
PostPosted: Fri Aug 07, 2009 1:09 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

hi jvmauricio,

I tried in deski xi r2 only and i got it correctly.

I have tried your solution in xi r3 it is giving #computation error.


Thanks,
Dominic Savio Thomas

_________________
DOSTH

Those who joyfully leave everything in God's hand will eventually see God's hand in everything
Back to top
bopradeep
Forum Member
Forum Member



Joined: 03 Aug 2009

Posts: 28


flag
PostPosted: Tue Aug 25, 2009 5:01 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

I have follwed As per Dominic Savio Thomas comments,

I got the table like this

Name,Var_Name,salary
prabu,prabhu,2000
jemes,jemes,1000
suresh,suresh,500
Others,Others,3000

but i am not able to change into pie chart in WebI Xir2. I don't know why i can't? whether it's have 3 column or some other issue, even i can't able to change into bar chart also.

Note: without Name field i can't able to create table structure also. if i try to remove Name field in table block then i am getting #context error.

Pls suggest me how to get the pie chart with others data in WebiXir2


Thanks & Regards,
Pradeep
Back to top
DoSTh
Senior Member
Senior Member



Joined: 31 May 2007

Posts: 82
Location: Bangalore


flag
PostPosted: Tue Aug 25, 2009 5:23 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

Hi,

I tried in deski r2 only.

I havent tried on webi.
I am sure that it wont work in webi.

Could you try the following ...
You may get something...

This
Let me know any of the solutions is working for you please.
Thanks,
Dominic[/url]

_________________
DOSTH

Those who joyfully leave everything in God's hand will eventually see God's hand in everything
This post has been reported for Wrong Forum. The current status is Closed / Resolved.
Moderator MichaelWelter closed this report Click for Details
Back to top
dinesh15787
Forum Member
Forum Member



Joined: 10 Jul 2014

Posts: 2
Location: United Kingdom


flag
PostPosted: Wed Jun 10, 2015 10:16 am 
Post subject: Re: Ranking in a table or chart for TOP N + OTHERS

Hi Everyone,

I have used this approach in the post and worked very well in the report. But i am facing an issue. Below is the detail of the issue.

I need to show top 5 causes based on claim count and the rest as remaining. The issue is when there are two same count values for Rank 6 (Remaining). Please suggest how to handle this scenario!

Thanks
Dinesh
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0380 seconds using 17 queries. (SQL 0.0093 Parse 0.0010 Other 0.0277)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo