Wednesday, 4 December 2013

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 like below:

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.


6 comments:

  1. can u describe more actually i m new in bo and i have know how to do this

    ReplyDelete
  2. can u describe more the variable actually i have same qry
    sample 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 same pie chart

    pls help

    Thanks in advance

    ReplyDelete
  3. This is so nice and clean. It looks fantastic. Thanks for posting.

    Business

    ReplyDelete
  4. Thanks for Information SAP BO (Business Objects) Module is one of the Largest Module in SAP offers a broad portfolio of tools and inbuilt applications designed to help you to easily optimize business performance by connecting individuals, data and businesses across the business networks. It additionally offers consulting and education services to assist the customers deploy its business intelligence tasks.SAP BO Online Training

    ReplyDelete
  5. A very interesting read and a great post alltogether. thanks for sharing this information.

    Criminal Lawyer Miami

    ReplyDelete