You can calculate your TopN / BottomN performers on the fly based on the filtered data.
New blocks on the Report data sources menu – Sort and Limit will allow you to display a ready-made queried, calculated, aggregated and ranked dataset in any view. If you wish to dynamically rank top 10 scores, while allowing the report viewer to exclude some data, then you will want to try this mechanism in your reports.
In the screenshot below, our original dataset with "All shipments" transactions has multiple operations applied :
- Live filtering : the Query block allows the report user to control the flow of data and include/exclude certain categories and data subsets from the calculation (filters applied on the original dataset "All shipments")
- Aggregation : the calculation is applied to 'Split field' categories (in our case we are calculating total per shipment)
- Ordering in the Sort block
- a limit is applied to determine the cut-off point for the resulting dataset (here to show just Top10 shipments).
Every view in this report will have access to this newly-created dynamic dataset "Top10 shipments".
Using the same procedure we could also produce bottom N ranking, and display this result next to the top achievers.
The Query block will automatically include 'filtered in' subset: any filtering on the original dataset fields (here "All shipments") will affect the data going into the ranking mechanism.
Note: If you add to the tab the filters for "Top10 shipments" they will filter the result of your dynamic ranking and not influence the flow of data going into this process.
In the Aggregate block you will need to specify the Grouping fields (e.g. what are you comparing - clients, years, workers) and the Measures field - the value you will use to measure their contribution. Here we are comparing volumes of traffic going to certain postcodes, so Grouping field is [PostArea] and Measure choice is [Pallets] sum.
The last two blocks will create ascending/descending order and determine your cut off point, so you can create Top 10 or Bottom 5 lists.
IOZ file with the project attached.