Show top N vs the rest in Spotfire

Introduction

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 achieve here.

Instructions

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.

Attachments

data.xlsx

show_top_n_vs_the_rest.zip