How to Learn the Spotfire Expression Language

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.

  1. Insert a calculated column into a data table.*
    1. Using a transformation.
    2. Using the Add Calculated Column option in the Data menu.
  2. 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.

Spotfire Version

Content created with Spotfire 10.2.

Related Articles

7 thoughts on “How to Learn the Spotfire Expression Language”

  1. Hi Julie, thanks for writing all these articles about Spotfire. I would argue that it is not a limitation of a calculated column, that it is not responding to filtering, it is actually a very valuable functionality and a good distinction between calc columns and custom expressions in an axis of a chart.
    There is a third way to use Spotfire‘s expression language: calculated columns as a transformation during data loading. It also creates a calculated column, but it is fix (aka frozen) so you can use it in other transformations e.g. unpivots. All three are following the exact same expression language and are part of one single software tool, so whatever you‘d like to achieve with your calculation, you only have to learn one syntax. And if you need even more, you can even use an inline TERR function as part of your expression as well…

    1. I definitely understand why Spotfire calculated columns are designed the way they are. However, I have yet to meet an engineer that thinks having calculations not respond to filtering is a feature rather than a bug. 🙂 You are right that adding a calculated column as a transformation is another way of using the expression language. I’ll update to reflect that. Thanks for the comment.

  2. Pingback: How to Use Axis Names in Spotfire Cross Tables » The Analytics Corner

  3. Christof,
    I was looking at this a bit closer. It used to be the case that if you wanted to use a calculated column in a pivot or an unpivot, it had to be created with a transformation. That is no longer a limitation, so I’m not sure it’s frozen anymore.

  4. Pingback: Use the Over Keyword to Slice and Dice Spotfire Calculations » The Analytics Corner

  5. Pingback: Using Node Navigation in Spotfire » The Analytics Corner

Leave a Reply

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