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.

pie1.JPG

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.

pie1.JPG

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.

    1. JEET PANDEY

      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
      (0) 
        1. Gaurav Saxena Post author

          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

          (0) 
          1. JEET PANDEY

            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

            (0) 
            1. Gaurav Saxena Post author

              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

              (0) 
              1. JEET PANDEY

                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

                /wp-content/uploads/2014/01/uni_359190.png/wp-content/uploads/2014/01/infoview_359191.png

                (0) 
                1. Gaurav Saxena Post author

                  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.

                  (0) 
                  1. JEET PANDEY

                    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

                    UNIVERSE_OTHERS.png

                    (0) 
                    1. Gaurav Saxena Post author

                      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

                      (0) 
  1. Mark Prosser

    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.

    (0) 

Leave a Reply