[ninja-popup ID=1094] Click here to download and follow along with the Spotfire file shown in this article [/ninja-popup]
I’ve been asked many times “How do I add units to columns in Spotfire?” It some cases it would be nice to have units next to a chart axis value, as shown in the picture below. Well, the truth is, there’s not a feature in Spotfire that allows you to specifically do this, but there are a couple of workarounds.
How to Show Axis Units in Spotfire – Option 1
The simplest option is just to include the units in your column name, as shown below.
Ex 1: Oil – BOPD
Ex 2: Oil (BOPD)
This makes it really obvious to anyone looking at your Spotfire project what the units are. The drawback of doing it this way is that while you can see the units on the axis selector, they don’t appear next to the axis values (scale labels).
You can rename the column in the source data table before loading into Spotfire, but the easiest option is just to rename the columns in Spotfire. To do that, follow the steps below.
- Click Edit > Column Properties
- Select the data table that your column is in from the drop down at the top of the Column Properties box.
- For example, if you want to add units to your Oil column, and that column is in the Prod Table, select Prod Table from the dropdown. If you only have one table in your Spotfire project, this area is blank.
- Navigate to the column by searching or scrolling through the list of columns. Click on the column you want to modify.
- Change the column name by editing the Name field.
- Click OK to apply the changes and close the window.
- Now the Axis Selector with the Oil column shows the units of ‘BBL’ with the column name. By editing the name at the Column Properties level, instead of the Visualization level, the units will show up every time that column is used in the future.
A Note about Column Names
You can always edit column names in Spotfire without worrying about losing linkage to your source data. Spotfire remembers the original column name, so the next time you refresh the data, it will still know which column to pull data from. To verify this:
- Open up the Column Properties and select the Oil column.
- Click the ‘Properties’ tab.
- Look at the the ‘External Name’. This is the column name as it exists in your source data.
How to Show Units in Spotfire – Option 2
The second option demonstrates how to show units directly next to the numbers, such as 100 BBL, as pictured at the top of this page. This is accomplished using Short Number Formats.
If you’re new to short number formats, or have never even heard of that feature before, check out this article (How to Create Custom Short Number Formats in Spotfire) to learn more. Short number formats are used on a chart axis to replace zeros with a symbol.
Ex: 1,000 = 1k
In above example, 10^3 is replaced with the symbol ‘k’. However, you can also use the power of 0. Anything raised to the power of zero is the original number.
Ex: 1,000^0 = 1,000
By telling Spotfire to replace 10^0 with your desired symbol (i.e. the units, ‘BBL’), essentially all you’re doing is adding units to the end of the number.
Steps to add Units via Short Number Formatting
- Open the Edit Symbol Sets options. Not sure how to get there? Click here and follow steps 1-3.
- Add a new symbol set
- Type ‘Oil’, since we’re going to use this symbol set for any axis with our Oil column. Click OK.
- Click ‘Add…’ to add a new symbol set. Leave the Exponent as 0, and type the units, BBL, into the Symbol box. Click OK.
- Repeat the steps above to add a new symbol set, this time for Gas, using ‘MCF’ for the units.
- Your screen should now look like the picture below.
- Click OK twice to get back to your active page.
Modify Axis Formatting
Now all you need to do is modify the Axis Formatting to apply the short number formats that you just created.
- Open the Formatting options in the visualization properties of the Oil chart
- Check the box next to ‘Use short number format’
- Select the ‘Oil’ symbol set that you just created
- Repeat Steps 1-3 for the Gas chart
Prefer to have Oil and Gas lines on the same chart?
No problem, we can easily accomplish this with multiple scales on the Y axis.
- Add Oil and Gas to the Y Axis selectors as separate columns
- Open the visualization properties
- Select ‘Y-axis’.
- Select ‘Multiple scales’. Select ‘For each color’
- Follow the steps above in Modify Axis Formatting to apply the short number formats to both Y Axis columns
- Close the Properties, and your chart should look like this:
Questions / Comments
If you have any questions or comments, or if there’s something you’d like featured on this blog, feel free to email me at email@example.com or leave a comment below.
[ninja-popup ID=1094] Click here to download the Spotfire file used in this article [/ninja-popup]