How to add “Others” in a pie chart with ranking in Webi
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(4th 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.
Can pie chart show only the top 3 or 4 without including others?
Pie chart will be showing 4, Top 3 plus others.
Nice Blog....
i wnat to work on this but i m unable to understand it can anybody will help me..
Ranjeet
i have same data
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
Hi Ranjeet,
Can you please explain in detail, what are you trying to achieve?
Thanks
Gaurav
Hi Gaurav
actually i am not understanding how to implement first 2 variables can you describe with my data
please help
Ranjeet
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
Hi Gaurav
I want top 3 in ranking and rest of all in "others" tab
final result is like that
I am using 3.1
Ranjeet
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
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
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.
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
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
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.