How to add “Others” in a pie chart with ranking in Webi

December 4, 2013 | 311 Views |

Gaurav Saxena

more by this author

SAP BusinessObjects - Web Intelligence (WebI)

Solution chart ranking;pie

How to Bring “Others” in a Pie chart when ranking is applied in the data.

Let’s say there is a set of Region and there Revenue like below:

Region	Revenue
East Japan	1,012,727
Bavaria	783,520
Mid-West	441,594
South	400,899
West	324,640
East Germany	259,752
West Japan	33,292
East Coast	19,124
Ruhr	10,976

My requirement is to get the TOP 3 Region based on Revenue and to display its percentage share from overall revenue. From below table we want to create a pie chart using Region and Percentage column.

Region	Revenue	Percentage_Share_overall
East Japan	1,012,727	30.81%
Bavaria	783,520	23.84%
Mid-West	441,594	13.44%
Sum:	2,237,841	68.09%

Pie chart always display total out of 100, in the above block, the percentage total is 68.09% only.

This will not come in a pie chart properly.

Option will be to show Other’s contribution and show in the pie chart.

For eg.

Region	Revenue	Percentage
Bavaria	783,520	23.84%
East Japan	1,012,727	30.81%
Mid-West	441,594	13.44%
Others	1,048,683	31.91%
Sum:	3,286,524	100.00%

This table can now be properly displayed in Pie chart.

Now comes the interesting part, how to bring value “Others” in the data.

I followed below steps:

Step: 1 Added an object in universe with Select statement as ‘Others’ and pointed it to Region from List of tables. Like below:

Step: 2 In the Webi Report which already has Region and revenue data, created another query selecting the newly created object (Region-Others) and revenue.

Step 3: Merge the object Region and Region-Others.

Step 4: Created four variables:

a. Revenue_variable=If([Region]=”Others”;[Query 1 (1)].[Revenue];[Query 1].[Revenue])

This variable is used to give data for others in the block along with other regions.

b. Variable1 = [Query 1 (1)].[Revenue]-(Sum([Query 1].[Revenue]) In Block)

This is used to subtract the value of all regions-sum of regions in block, which will assign others data.

c. Variable2=If([Region]=”Others”;[Variable1];[Revenue-Variable])

This issued to display the values for all regions including “Others”.

d. Final Percentage=([Variable2]/Sum([Variable2]) In Block )

This is used for calculating percentage.

Region	Revenue-Variable	Variable1	Variable2	Percentage
Bavaria	783,520	-3,286,524	783,520	0.24
East Coast	19,124	-3,286,524	19,124	0.01
East Germany	259,752	-3,286,524	259,752	0.08
East Japan	1,012,727	-3,286,524	1,012,727	0.31
Mid West	441,594	-3,286,524	441,594	0.13
Others	3,286,524	0	0	0
Ruhr	10,976	-3,286,524	10,976	0
South	400,899	-3,286,524	400,899	0.12
West	324,640	-3,286,524	324,640	0.1
West Japan	33,292	-3,286,524	33,292	0.01

Step 5: Applied ranking based Revenue_variable. Although I need Top 3 , I selected Top 4(4^th for the Others)

Region	Revenue-Variable	Variable1	Variable2	Percentage
Others	3,286,524	1,048,683	1,048,683	31.91%
East Japan	1,012,727	-2,237,841	1,012,727	30.81%
Bavaria	783,520	-2,237,841	783,520	23.84%
Mid West	441,594	-2,237,841	441,594	13.44%

Step 6: Using region and Percentage turned it to Pie chart.

So the pie chart is correct now.

Note: If you have query filters in Query 1, apply same filter in query 2 also.

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

Sachin Kashikedar December 4, 2013 at 9:05 am

Can pie chart show only the top 3 or 4 without including others?

like (0)
1. Gaurav Saxena Post authorDecember 4, 2013 at 9:22 am
  
  Pie chart will be showing 4, Top 3 plus others.
  
  like (0)
Daya Jha December 4, 2013 at 9:09 am

Nice Blog….

like (0)
JEET PANDEY January 6, 2014 at 9:31 am

i wnat to work on this but i m unable to understand it can anybody will help me..

Ranjeet

like (0)
1. JEET PANDEY January 6, 2014 at 9:33 am
  
  i have same data
  
  country total
  
  Asian/Pacific Islander 2,923
  
  American Indian or Alaskan Native 1,772
  
  Black 3,591
  
  Hispanic 11,039
  
  White 38,241
  
  Other/Unknown 1,990
  
  and i want rank with other tab pls help me
  
  thanks in advance
  
  like (0)

JEET PANDEY January 6, 2014 at 12:10 pm

http://scn.sap.com/community/businessobjects-web-intelligence/blog

I tried with this blog but i m not understanding can anyone help me to understand the variables.. in this blog

Ranjeet

like (0)

Gaurav Saxena Post authorJanuary 7, 2014 at 12:36 pm

Hi Ranjeet,

Can you please explain in detail, what are you trying to achieve?

Thanks

Gaurav

like (0)

JEET PANDEY January 7, 2014 at 6:36 pm

Hi Gaurav

actually i am not understanding how to implement first 2 variables can you describe with my data

please help

Ranjeet

like (0)

Gaurav Saxena Post authorJanuary 8, 2014 at 5:14 am

Hi Ranjeet,

In your data what is the “others” signify and also what is the rank you want to apply.

Can you please be more descriptive about your exact requirement, what exactly you want to see as final result?

Thanks

Gaurav

like (0)

JEET PANDEY January 8, 2014 at 6:32 am

Hi Gaurav

country		Total
American Ind or Alaska Native		1
Asian		1
Asian Indian		2
ASIAN/PACIFIC ISLANDER		171
Black or African American		6
Hispanic Or Latino		4029
Korean		1
Other Race		1
Vietnamese		1
WHITE		734
WHITE, BLACK, AMERICAN INDIAN		45024

I want top 3 in ranking and rest of all in “others” tab

final result is like that

WHITE, BLACK, AMERICAN INDIAN

90.12%

Hispanic Or Latino

8.06%

WHITE

1.47%

Others

0.34%

I am using 3.1

Ranjeet

like (0)

Gaurav Saxena Post authorJanuary 8, 2014 at 7:06 am

Hi Ranjeet,

Before creating a variable in the report.

1.First create an object in universe say Country-Others and in select box mention “OTHERS”.

2.Now in report create another query and bring objects Country-Others and your measure value.(keep in mind same set of filters and prompts need to be present here as you have defined in query 1 or your original query)

3.Merge your object country with country-others.(lets say merged name is Country_1

4.Now create the variables:

a.       Variable-1=If([Country_1]=”OTHERS”;[Query 2].[measure];[Query 1].[measure])

This variable is used to give data for others in the block along with o OTHERS.

b.      Variable-2 = [Query 2].[measure]-(Sum([Query 1].[measure]) In Block)

This is used to subtract the value of all country-sum of countries in block, which will assign OTHERS data.

c.       Variable-3=If([Country_1]=”OTHERS”;[Variable-2];[Variable-1])

This issued to display the values for all regions including “Others”.

d.      Final Percentage=([Variable-3]/Sum([Variable-3]) In Block )

Now apply ranking in this block for Top 4.

Check if you are getting the desired results.

Thanks

gaurav

like (0)
1. JEET PANDEY January 8, 2014 at 2:03 pm
  
  Hi gaurav
  
  i have made as per you mention but it is not working i am attachment the final result
  
  variable as
  
  variable1=If([Country_1]=”OTHERS”;[Query 2].[TOTL_CNT];[Query 1].[TOTL_CNT])
  
  variable2= [Query 2].[TOTL_CNT]-(Sum([Query 1].[TOTL_CNT]) In Block)
  
  variable3=If([Country_1]=”OTHERS”;[Variable-2 ];[Variable-1])
  
  final percentage=([Variable-3]/Sum([Variable-3]) In Block )
  
  please have a look and revert if i m not applying right method
  
  like (0)
  1. Gaurav Saxena Post authorJanuary 9, 2014 at 6:11 am
    
    Hi Ranjeet,
    
    Can you please use column Country_1 in the blocks instead of Cd_val_Nm,as OTHERS value will come in merged data only not in sungle data provider columns.
    
    Also can you add screeshot of the two query panels of your report.
    
    like (0)
    1. JEET PANDEY January 9, 2014 at 7:19 am
      
      HI GAURAV,
      
      AT UNIVERSE LEVEL when i m going to put “OTHER” in select statement it is not parsing
      
      please check i am attaching the snap shot
      
      like (0)
      1. Gaurav Saxena Post authorJanuary 9, 2014 at 8:40 am
        
        Probably because in Teradata ext under doublequotes is considered as Keyword.
        
        Can you try using single quotes ‘OTHERS’. also in the tables button point it to the same table from where you derived the country.
        
        And even if parsing fails try to see , whether query in the report is running and retrieving the value OTHERS as result.
        
        Gaurav
        
        like (0)

Mark Prosser January 20, 2014 at 1:57 pm

This all seems a bit over-complicated.

Create a new dimension variable as:

=If(Rank([Revenue])<=3;[Region];”Others”)

Now use this variable together with revenue in your pie chart.

like (0)