TOP N & ALL OTHER in BOBJ 4.0 Dashboard Design

[Former Member]

Hi All,

We are SAP BW using BEx Queries as the datasource via BOE in Dashboard design.

Does any one have any idea on doing Top 10 or N on customers and show All Other and plot in the column graph.

Requirement:

Show table on Top 10 or N based on "Total" Volume. Metrics the table need to shows is Volume and Growth Rate.

                                                          jan feb mar apr may jun jul aug sep oct nov dec   q1   q2    q3   q4    total

Customer N         Act Fcst Vol          100 200 100 100 50 50 50   30 30    40  50     50   400 200 110 140   850

                              GR%

                              Next Year GR%

Customer N-1        Vol

                               CY GR%

                    Next Year GR%

Customer N-2         Vol

                                GR%

                     Next Year GR%

Customer N-3         Vol

                                  GR%

                      Next Year GR%

:

:

TOP 10 Total : Vol

                         GR% (% shouldnt add up)

        Next Year GR% (% shouldnt add up)

Graph 1 : N customer & All Other in X axis and Volume on Y Axis

Click on each customer and show graph on current growth rate and next year growth rate by month in x axis and % in y axis.

Please let me know if it can be done in BOBJ 4.0 Dashboard design.

Thanks

[Former Member]

Hi Sini,

Did you get the solution..?

Regards

Subbarao

[Former Member]

No, I dint get to work.

My problem is rank is based on total YTD value and display by month across and also show YTD total.

so i have a keyfigure called "Qty" and a characteristics calender month "jan, feb....mar...dec" and also show the sum all of calender month which is what i have to rank it on.

so i keep different issues with No filter.

any advice....may be i am makeing it over complicated.

[former_member190855]

Hi Sini

How big is your data set before filtering by rank? If it is within 100-150 did you try the ranking within Xcelsius the way I suggested in a previous reply?

Also, if it is something has to be soled at the WebI level then you may want to post it to the WebI space.

Runali

[Former Member]

Hi Sini,

For building the Dashboard you need specific Design (Spreadsheet,chart....components etc)

design your dashboard first (i mean fix/choose the components to display)

=>Design your Excel sheet (ex: chart with x-axis month and y-axis vol)

Now you need to build the webi report according to the requirement that suits your excel design.

                        (or)

=>Design your webi report that suits your Dashboard and bring the data to excel to map with components

Don't consider the  Bex report format/layout.

Note: You should consider only data in webi whether it is right data or not.

Regards

Subbarao

[Former Member]

How do I get this kind of layout in WebI

                                       Jan  Feb mar........Dec Q1 Q2 Q3 Q4 H1 H2  Total

Customer 1      Metric1

                         Metric 2

Customer 2     Metric 1

                        Metric 2

Top 2 Sum      Metric 1

                        Metric 2

All Other          Metric 1

                         metric 2

Grand Total     Metric 1

                         Metric 2

Top is based on Total on Metric 2.

[Former Member]

HI Sini,

Use Cross tab table

Regards

Subbarao M

[Former Member]

Hi Sini,

In order to achieve the below requirement

I need Top N list

- Top N Sum (Note there is % too which is not a sum and it needs to do row by calculation)

- All Other Sum & %

- Grand Total.

you need to create a variable in webi report using the formula

Rank(measure;[ranking_dims][;Top|Bottom][;(reset_dims)])

in your case take a variable rank=rank([vol];[customer])

for getting remaining others after top 5 or 10 you need to create a variable in webi

as customer = if(rank>5)then "others" else [customer]

now you add this variable in the table and hide the existing [customer]dim

so that you will get the required Top 5 customers and Others -Total and Grand Total in your report....

Let me know the result once after trying the above stpes

Regards

Subbu

[Former Member]

In BW query I have two structure.

One struture is the keyfigures

and the other structure is the months going across the columns. When I import Bex query to WebI the months is showed as "Struct."

And in the struct that shows i have jan, feb...dec, Q1, q2...Q4, h1, h2 and total. I need to sort on total

[Former Member]

The format of the report is as shown below.

                                          Jan  Feb mar........Dec Q1 Q2 Q3 Q4 H1 H2  Total

Customer 1      Metric1

                         Metric 2

Customer 2     Metric 1

                        Metric 2

Top 2 Sum      Metric 1

                        Metric 2

All Other          Metric 1

                         metric 2

Grand Total     Metric 1

                         Metric 2

Top is based on Total on Metric 2.

The above is the report format.

[Former Member]

Subbarao,

For the below scenario you suggested i am having the following issue.

***How did you add Other. In my report others are repeating in all rows of customers. How do i aggregate that.****

Customer = if(rank>5)then "others" else [customer]

now you add this variable in the table and hide the existing [customer]dim

[Former Member]

Hi Sini,

you can get the sum of all others using the below formula

=NoFilter(Sum([Measure])Where ([rank test]>5 ))

Since Others are having rank more than "5" 

Regards

Subbu

[Former Member]

Hii Sini ,

Top N Condition applied in Bex Query is not supported in Dashboard Design hence to overcome this problem we need to apply a trick as mentioned below .

Step 1 : Ask the BW Consultant to Remove TOP N Condition based on a particular KF from Bex Query .

Step 2 : Bex Report should be sorted based on KF in Descending Order .

Step 3 : Bind just 10 Cell in Excel Range in Dashboard Design .

Hope using above logic you will get Top N Report in Dashboard or there is a Pre Query Option in BW

for BW Consultant has to look upon.

Regards

Jeetan Jagtap

[Former Member]

What I need is not only Top N

- I need Top N list

- Top N Sum (Note there is % too which is not a sum and it needs to do row by calculation)

- All Other Sum & %

- Grand Total.

Also all the above needs to change based on my dropdown or checkbox on all other characteristics.

How do I do All Other in WebI.?

If I could do that in WebI and use that as a datasource in Xcelsius will I be able to do filtering on other characteristics as dropdown/ checkbox in Xcelsisus.

Thanks

[former_member190855]

Hi Srini

Within WebI you have to use the Ranking feature which will give you TopN value and then NoFilter function to calculate the other values in your list. But I am not sure N can be a variable in that case.

If you are using a any filter within Xcelsius to choose the value of N... I am guessing user selects either top 5/10 etc what they want.

Method 1: You may have to get that done within Xcelsius. Get the data sorted in the order by which you determine topN. Add a column left to the datablock which should go from 1,2,3-100 (say you have 100 rows in your datablock). Add another column and write a formula if N > 1(the cell name with value 1) then "X" else "Y"and drag that formula to all the way down. Then use a combobox to filter all the rows matching X for top 10 and Y for all others.

Method 2: Create separate WebI blocks for all possible values for ranking if that is a limited set and call the WebI block based on value of N

Let me know if you need further clarification.

Thanks

Runali

[former_member190855]

Hi Sini

Did it work?

Runali

[Former Member]

Hii Sini ,

kindly share the exact Report format or snapshot which will clarify the exact requirement to get it resolved. .

Regards

Jeetan Jagtap

[Former Member]

Jeetan,

Regarding point:

Step 2 : Bex Report should be sorted based on KF in Descending Order .

How do I set sorting on KF directly in a BW query? I thought it was only possible once the query was run in the Analyser?

[Former Member]

Hi Agata,

Go to the Display option in Bex Query Designer and set the following properties:

Sort Characteristic"--> Select the characteristic and

"Sort by" --> Select "Text" and

"Sort Direction" --> Select  "Ascending/Descending" as per report requirement .

Regards

Jeetan

[harshil_joshi]

Make a table with your Top 10 requirements.

Now click sum of key figure, which adds one new row with sum.

Clear Contents from this row.

Now in first column add 11 and in another column add Other All.

Now in another column =NoFilter(Key Figure which you want to display)  - Sum(Key Figure which you want to display)

Thanks.

[Former Member]

HI Sini,

There is limitation on TopN condition from BEx side.

To have TopN condition in place, you need to apply the logic on reporting side.

You may create a WEBI report and apply TopN condition on it and then use that report in Dashboards Design using BIWS or LiveOffice data connection.

Regards,

Tejas