How to Create Custom Short Number Formats in Spotfire

[ninja-popup ID=929] Click Here to download the Spotfire template shown in this article![/ninja-popup]

This article explains how to create custom short number formats in Spotfire, specifically using standard oilfield abbreviations.

When you’re working with large numbers on the axes of your visualizations, sometimes all of the zeros or extra digits can take up unnecessary space and become distracting, wasting valuable real estate on your dashboard.

In Spotfire, there’s a formatting option to use “Short Number Formats”, shown in the screenshot below. This feature shortens the number displayed by replacing zeros with a letter, such as “k”. For example, the short number format of 10,000 is 10k. This is a really useful feature to clean up the chart axis and remove extra digits, however, not all industries use the same abbreviations. This can cause confusion or misinterpretation of the visualization.

Picture 1 - Spotfire Axis Formatting Options

The default setting in Spotfire is to shorten 10^3 with “k” and 10^6 with “M”. For example, 1,000 would be 1k and 1,000,000 would be 1M. This can be confusing in the oilfield, since we’re used to representing 1,000 with ‘M’ and 1,000,000 with ‘MM’.

1,000,000 = 1,000 M = 1 MM

Fortunately, there’s an option to create custom short number formats – I’ve outlined the steps below.

The 3 screenshots below demonstrate the difference between standard formatting, the default short number formatting, and custom short number formatting. Notice how the values on the Y axis are changing in each picture.

Picture 2 - Spotfire Axis Formatting Examples

 

How to Create a Custom Short Number Format

  1. Click Tools > Options
  2. Select “Axis Formatting”
  3. Click box labeled “Edit Symbol Sets”

Picture 3 - Spotfire Axis Formatting Options Screen

  1. Click “Add…” to add a new Symbol Set, which will be used as the short number format. Symbol Sets are defined as the combination of a Factor, which is 10 to the power of a number, or 10^n, and a Symbol, which is a letter or combination of letters.

Picture 4 - Spotfire Add New Symbol Set Naming Screen

  1. Type a name for this Symbol Set. I’ve called mine “Oilfield Units”. Then Click OK.

Picture 5 - Spotfire Custom Symbol Set Options Screen

  1. Click the “Add…” box on the right next to the “Define symbols” window.
  2. In the box that pops up, type an exponent and symbol, then click OK. The exponent defines the power of 10 you want to replace with a symbol when using the short number formats. To replace 10^3 (1,000) with the symbol ‘M’, enter 3 in the exponent field and M in the symbol field. The number 1,000 will then be displayed as 1M, 2,000 as 2M, and so on.
    1. Note: If you want a space between the numbers and letters on your axes, make sure to add a space before the symbol, as shown below.

Picture 6 - Add a new symbol set in Spotfire

  1. Repeat Steps 6-7 for other exponents, such as 10^6 (MM) and 10^9 (B).
  2. When you’ve added all the exponents, it should something look like this:

Formatting an Axis with a Custom Short Number Format

After defining your new, custom “Oilfield Units” short number format, update the formatting on your visualization axis or column property.

  1. Open the visualization properties
    1. Right click on a visualization, click properties
  2. Select Formatting from the list of options on the left
  3. Choose an Axis from the box
  4. Select “Number” under Category
  5. Check the box next to “Use short number format”
  6. Select “Oilfield Units” (or whatever name you gave it) from the dropdown

Picture 8 - Format Axis with Custom short number format in Spotfire

After updating the axis formatting with the new custom short number format, your chart axis now looks like this:

Picture 9 - Spotfire axis with oilfield units3

Summary
Short number formats can be really useful in optimizing space and making your axes look cleaner. Customizing the format makes your visualizations look more professional and intuitive for your audience.

Here’s a summary of the different number formats shown in this article:
Picture 10 - Summary Table of Spotfire Short Number Formats

If you have any questions or comments, feel free to email me at kyle@datafuel.co, or leave a comment below.

[ninja-popup ID=929] Click Here to download the Spotfire template shown in this article![/ninja-popup]

Next Post: How to add units to a chart axis like the screenshot below:

Picture 11 - Showing units on an axis in Spotfire

Leave a Reply

Your email address will not be published. Required fields are marked *