Business Intelligence Tools

Spotfire – Insert, Calculated Column vs Insert, Transformation, Calculate New Column

  • Have you experienced issues joining with calculated columns?
  • Have you experienced issues getting calculated columns to work with pivot and unpivot?
  • Have you ever wondered why insert, transformations, calculated column is even an option?

For this week’s post, I want to discuss calculated columns.  There are two ways to insert calculated columns into a data table:

  1. Insert, Calculated Column
  2. Insert, Transformation, Calculate new column.

There is a key difference between the two, and that is when they can be updated.  Transformation steps are performed only when an analysis is first opened and when data sources are refreshed.  This makes them more “stable”, for lack of a better word, than creating calculations via Insert, Calculated Column.  When you insert a calculated column, it can theoretically change at any point in time, especially if property controls are incorporated.  For example, if you used a calculated column that included an input property control to join with, anytime the input changed, the table would need to be reconstructed.  Therefore, calculated columns cannot be used with joins or with pivot/unpivot. They will simply not be presented as an option to work with.

The screenshots below show a data set with a column called BOE that is a calculated column.  You can also see the attempts at pivoting and inserting columns.  BOE (calc) does not show up as an option.

insert-column pivot

Thus, if you want to join on a calculated column or use a calculated column in a pivot/upivot (even if it’s just to pass through), you must create those calculations as transformations.

4 thoughts on “Spotfire – Insert, Calculated Column vs Insert, Transformation, Calculate New Column

  1. tze yik Reply

    Your post of difference between transformation & insert column are very helpful.

  2. Miguel Ángel Reply

    Thanks so much.

    And about performance? Is there any difference between both methods?

    • Julie Sebby Post authorReply

      I’m not sure. I have not done pointed performance testing.

    • Julie Sebby Post authorReply

      I do know that there are settings in the Admin manager to control whether calculation results are stored or not and that affects performance.

Leave a Reply

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