Since launching the Analytics Corner, I’ve focused heavily on IronPython with a dash of Alteryx. I’ve started working more with Axis Names recently, which was a reminder of how nonintuitive they are. Because I haven’t written much on Spotfire expressions since starting the Analytics Corner, I’m going to do a comprehensive series on how to learn the Spotfire expression language, which will include a good section on Axis Names.
Today’s first post will be a short summary of the different ways to write expressions in Spotfire. Next week, I’ll talk about the over keyword. Then, I’ll cover node navigation, which might take more than one post. I will also show you how to use important functions like $esc and $map. Finally, I’ll close out with a few posts on Axis Names. Read on to get started.
Calculations in Spotfire
Depending on how you look at it, there are 2 – 3 ways to write calculations in Spotfire.
- Insert a calculated column into a data table.*
- Using a transformation.
- Using the Add Calculated Column option in the Data menu.
- Write an expression on the axis of a visualization.
*Both 1 and 2 will insert a calculation into a data table.
It’s very easy to write simple expressions on the axis as shown below. However, writing more complex expressions can quickly become frustrating.
Thus, most new users default to inserting calculated columns. Although, they will quickly run into limitations.
It used to be the case that if you wanted to use a calculated column in a pivot or unpivot, it had to be added as a transformation. However, that is no longer a restriction, and calculated columns added via the Data menu can be used in pivots and unpivots without limitation.
Thus, 1.1 and 1.2 are only different in terms of the order of operations. 1.1 happens as data is coming into Spotfire and because of that, you may order it with other transformations. For example, you could create a calculated column using a transformation and then change that column’s data type. Or, you could create a calculated column and then filter data out of the data set. Otherwise, 1.1 and 1.2 are the same.
Limitations of Calculated Columns
The biggest limitation of writing calculated columns to a data table is that the calculation result uses all data in the table. In other words, calculated columns do not update or respond to filtering. For example, this calculation will add up all GasProd for each region. However, that number will not change if you filter the date range.
Sum([GasProd]) over [Region]
For a long time, I used a pass thru data functions to get around this limitation. However, once you learn how to use Axis Names and write expressions on the axis of a visualization, this becomes unnecessary.
I know that wasn’t much this week, but in the coming weeks you’ll great content to help learn the Spotfire expression language, including Axis Names.
Content created with Spotfire 10.2.