Show top N vs the rest in Spotfire
By:
Last updated:
3:03am May 24, 2016

Sometimes it is useful to visualize the Top N of something, versus "the rest". This is relatively easy to do in Spotfire but requires a custom expression on the X-axis, which we will see how to acheive here.

But first lets look at some example data:

State    Year    Value
Alabama    2012    251
New York    2012    183
Maine    2012    129
Florida    2012    265
California    2012    269
Washington    2012    241
Oregon    2012    167
Nevada    2012    150
Georgia    2012    116
Alabama    2013    245
New York    2013    230
Maine    2013    116
Florida    2013    290
California    2013    127
Washington    2013    211
Oregon    2013    158
Nevada    2013    268
Georgia    2013    138
Alabama    2014    128
New York    2014    272
Maine    2014    139
Florida    2014    174
California    2014    169
Washington    2014    219
Oregon    2014    101
Nevada    2014    190
Georgia    2014    203

Now if we want to create a barchart which shows the top 5 states versus the rest, we need to:

1. Create a barchart with Sum(Value) on the Y axis and [State] on the X axis

2. Edit the expression on the X axis to:"<If(DenseRank(Sum([Value]) OVER ([State]),"desc")<=5,[State],"The Rest")>"

3. In order to make sure the Top 5 is calculated dynamically, set the X-axis to "Evaluate Axis on-> Current filtering only" (from the properties for the Category Axis, or by right click on the category(X) axis scale).

You should now be all set and see a barchart as below:

 

You may want to add color to the chart as well, by using State on the color by axis:

Please find attached both the data used and the DXP file I used to create the screenshots.

Hope you find this useful and love to hear your feedback in the comments section.

 

Attachments

AttachmentSize
File data.xlsx9.65 KB
Package icon show_top_n_vs_the_rest.zip165.85 KB

Feedback (1)

This is a great visualization technique when you have a “long tail” type distribution with a few larger categorical values and many smaller ones. In order to improve readability of a chart people will often apply simple filters to only show the top categories, but then you lose all context about the smaller categories, which may collectively make up a meaningful portion of the total. This technique allows you to setup a chart which highlights the top categories while also providing context on how those top categories relate to the sum of all the smaller ones.

This can be particularly effective with pie charts which often look very messy when you have too many small slices. By dynamically grouping all the smaller slices into an “other” category, you can achieve a nice clean looking pie chart while still accurately reflecting each slice’s proportion of the total.

Would be nice to see this as a built in feature in a future version of Spotfire.

aaron.rubinstein 8:55am May. 24, 2016