How to Perform Calculations in Spotfire Cross Tables – Part 1

Cross Tables are a great way to quickly summarize data and evaluate relationships between different variables in Spotfire. If you’ve ever worked with Pivot Tables in Excel, then you’ve probably recognized that Spotfire Cross Tables are very similar.

[ninja-popup ID=1930] CLICK HERE To download the Spotfire template shown in the video. [/ninja-popup]

1-Spotfire_Cross_Table_with_Calculation

The challenge with Cross Tables is that it’s difficult to perform calculations between columns, such as the “Type 1 – Type 2” column in the screenshot above. There is a feature to calculate the Grand Total of all columns, but no other mathematical operations are available.

Let’s say you have a Cross Table with 2 columns, as in the screenshot below. You might want to take the difference between these two columns to see which column value is greater, and by how much.

2-Spotfire_Cross_Table_Example

With only 4 rows, this is a simple example and you could probably approximate the result in your head, but with more rows of data and more complex values and calculations, this becomes difficult. Also, having a separate column with calculated values is useful for sorting and coloring.

Here are some other examples of calculations you may want to perform in a Cross Table:

  • Subtracting one column from another
  • Adding two or more columns
  • Taking the average of multiple columns
  • Calculating the percentage of one column to the total

I have received several questions about performing calculations in Spotfire Cross Tables and would like to share a few different methods for doing this. This article explains one method, and the future I will demonstrate additional methods for performing Cross Table calculations. Be sure to check back to see those!

The video below describes one method for performing calculations in a Cross Table using a Pivot Transformation and Custom Expression. Check out this video to see the exact steps to achieve the final result shown in the first screenshot above.

If the embedded player above isn’t working, this video is also on YouTube: https://www.youtube.com/watch?v=zK8AD77PblQ

[ninja-popup ID=1930] CLICK HERE To download the Spotfire template shown in the video. [/ninja-popup]

Questions / Comments
I hope this has been a helpful demonstration of Cross Table calculations in Spotfire. If you have any questions or comments, or if there’s something you’d like featured on this blog, feel free to leave a comment below or email me at kyle@datafuel.co.

7 thoughts on “How to Perform Calculations in Spotfire Cross Tables – Part 1

  1. Santosh Krishnan Reply

    I was wondering if you could assist me with a query on Spotfire. I will explain the scenario here:

    I have a data set which I am using to do some graphs. Certain columns of this data set have been used as filters to enable the users to cut and slice the data according to their needs. I also have a Cross table which comes from the same data set and uses the same filters. However, I want the cross table to change on the selection of certain filters but not all. I have tried various options; but with no luck.

    Any assistance you can provide will be much appreciated.

    • Rohit Bhatia Reply

      Santosh, you can use the concept of Filter organization. Create a dummy filter organization. In this add the filters that you need. Now go to the properties of the cross table and use the limit data using filtering property in ;Data’ subsection.
      Select the filtering scheme that you have created.
      Make sure that the filter you need the cross table to be filtered by, are from that filter scheme when you add them to the text area.

  2. Lakshmi Srikanth Reddy Reply

    Hi,
    I have a senerio like this,

    I want to implement Sum( Material cost) per bike/ Sum(material cost for all the bike) per quarter,

    how can we implement this in Spotfire Cross table.

    • Kyle LaMotta Post authorReply

      Based on the information provided (and not knowing what the rest of your data looks like), I think this will work: Insert 2 calculated columns, One to calculate the total material cost per bike, per quarter:

      Sum([Material Cost]) OVER ([Bike ID], [Quarter])

      and a second column to calculate the total material cost per quarter for all the bikes:

      Sum([Material Cost]) OVER [Quarter]

      Then use those 2 columns in a custom expression on Value axis of the Cross Table, with Quarter on either the Column or Row axis. If you don’t have a column for Quarter, you can calculate that as well:

      Quarter([Date Column]) & "-" & Year([Date Column])

    • Kyle LaMotta Post authorReply

      It has to do with the rounding. That chart is formatted with no decimals, but if you add a decimal the values would be:

      347.7 – 440.3 = -92.6, which rounds to -93 with no decimals
      409.4 – 368.6 = 40.8, which rounds to 41 with no decimals

Leave a Reply

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