Top N Sets
Some dimensions have a very high cardinality making it difficult to visualize them. In such cases, one might focus their analysis on the top N items only.
Top N by aggregates
single dimension
multiple dimensions
nested
Top N by non-aggregates
Usually Top N is done for aggregate data but SQL Frames allows reporting on non-aggregate data. This gives the ability to find the top N transactions.
single dimension
This example gives the top 2 transactions by Revenue per Country
Notice how the Country is provided in the groupBy() rather than as the 3rd parameter to topN()
(as in case of aggregates example above). The reason for this is that in case of non-aggregates
there is always an implicit row dimension (or a logical unique id) by which the ranking happens.
multiple dimensions
This example gives the top 3 transactions by Revenue per Region and Item Type.
nested
The following gives the top 3 transactions within each 'Item Type' and 'Region'. This is similar to the above multiple dimensions example except, it also has a Group Rank. That is, the rank of the transaction within the group.
As mentioned above, by default there is an implicit row dimension for non-aggregates. Hence
the 3rd parameter of topN can be used as a sub-group and the top N filtering happens based
on the ranking within the sub-group.
Contrast this for aggregate top N where the sub-group parameter is used to aggregate the data first to that level and then do the ranking. Since there is only one aggregated record for that sub-group, the top N filtering always happens based on the group ranking for aggregates while for non-aggregates the filtering happens by rank within the group or the sub-group if one is specified.
Group ranking is calculated after filtering by the sub-group rankings.
A blank groupBy (not calling the groupBy() API at all) provides global ranking.
overall
This example gives the top 5 transactions by Revenue among all the transactions.
Top M within Top N
This report provides top 3 Item Types of top 5 Countries.
Since SQL Frames brings all the declarative power of SQL as a low-code API, it is possible to
create more advanced reports such as this. That is why it is so important to think SQL and then simply
use SQL Frames API to craft the logic as per that SQL.
Top N with DataFrame Slicer
Top N reports can be made dynamic using the DataFrame Slicer.
Other Sets
Bottom N
All the above API is also applicable to df.sets.bottomN API which shows
the bottom N.
Extreme N
All the above API is also applicable to df.sets.extremeN API which
shows the top N and bottom N together.