on 08-21-2013 9:36 PM
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
Hi Sini,
Did you get the solution..?
Regards
Subbarao
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.