Description:
You can use rank to generate a report that display for example top 10 customers based on sales information or bottom 10 branches based on their profit. To do that in BO we have 2 Methods.
[Method # 1]: Apply Rank from the Webi report
In this method you can click on Apply/ Remove rank button (
) to add rank on the selected column.
This will open the following Wizard to set Rank properties:
Ranking Properties:
In ranking properties you can select your ranking direction. if you want to display top records in your ranked list then just tick check box beside Top. If you want to display records at bottom of your list then select Bottom. You can also enter the number of records that you want to display. for example if you select Top and entered 10 in the text box beside it then it will display top 10 records if you selected count as a calculation mode.
Based on:
select the measure that will be used to rank your records. You may have more than one measure displayed in your table but you can only rank them based on one of them.
Ranked By:
Select the dimension associated with this rank.
Calculation Mode:
- count: will stop ranking after it reach for that count. For example if you selected Top 10 with count as calculation mode it will keep displaying records on top of your list till the 10th record.
- Percentage:
- Cumulative sum: it will keep displaying records till reach the limit specified in ranking properties.
- Cumulative Percentage:
Example:
Find below the raw data that we will use in our example:
| Branch | Transaction Amount | Transaction Volume |
| 101 | 50,000 | 100 |
| 102 | 1,000,000 | 600 |
| 103 | 60,000 | 250 |
| 104 | 950,000 | 700 |
| 105 | 150,000 | 460 |
| 106 | 2,000,000 | 800 |
| 107 | 200,000 | 300 |
| 108 | 500,000 | 620 |
| 109 | 350,000 | 430 |
| 110 | 1,900,000 | 850 |
Now lets check the following ranking cases:
Case # 1:
Description: Display top 5 branches based on their transaction volume
Ranking Properties:
Top [ 5 ]
Based on [ Transaction Volume] Ranked by [ ]
Calculation Mode: Count
Results:
| Branch | Transaction Amount | Transaction Volume |
| 110 | 1,900,000 | 850 |
| 106 | 2,000,000 | 800 |
| 104 | 950,000 | 700 |
| 108 | 500,000 | 620 |
| 102 | 1,000,000 | 600 |
Case # 2:
Description: We need to display branches that performed 80% of total transaction volume
Ranking Properties:
Top [ 80%]
Based on [ Transaction Volume] Ranked by [ ]
Calculation Mode: Cumulative Percentage
How it Works:
| Branch | Amount | Volume | % | Cum % |
| 110 | 1,900,000 | 850 | 17% | 17% |
| 106 | 2,000,000 | 800 | 16% | 32% |
| 104 | 950,000 | 700 | 14% | 46% |
| 108 | 500,000 | 620 | 12% | 58% |
| 102 | 1,000,000 | 600 | 12% | 70% |
| 105 | 150,000 | 460 | 9% | 79% ** |
| 109 | 350,000 | 430 | 8% | 87% |
| 107 | 200,000 | 300 | 6% | 93% |
| 103 | 60,000 | 250 | 5% | 98% |
| 101 | 50,000 | 100 | 2% | 100% |
** Limit Achieved
Results:
| Branch | Amount | Volume |
| 110 | 1,900,000 | 850 |
| 106 | 2,000,000 | 800 |
| 104 | 950,000 | 700 |
| 108 | 500,000 | 620 |
| 102 | 1,000,000 | 600 |
| 105 | 150,000 | 460 |
Notes:
- We ranked our data based on Volume and we didn’t consider Transaction amount in our calculation.
[Method # 2]: Apply Rank from the Edit Query window
In this method you will retrieve only the ranked data from the database.
No comments:
Post a Comment