Visualisation of cross tables in Tibco Spotfire

Question

Is it possible to build a chart based on cross tables? The data table I import from Excel is already set as a cross table and I'm trying to visualize it as a line chart, but can't get how to set X-axis based on a row, not a column. For example, I have a table

Var Item Year1 Year2 Year3
1 Apple 1 2 3
1 Orange 1 1 1
2 Apple 2 2 3
2 Orange 3 3 3

And I want to display items through years, filtering variables. Is it possible at all? Thanks in advance!

Answer 1

The problem is that you have a pivoted data set. You can add an Unpivot transformation when you load your data into Spotfire, which will make the data easier to plot on a line chart.

I copied your sample data set. Then in Spotfire, went to Add Data Table, and clicked the 'Add' button and selected Clipboard. Then I went to the 'Transformations' section toward the bottom of the Add Data Tables form, and added an Unpivot transformation (this pic show what I'm talking about).

On the Unpivot Data form, I added 'Var' and 'Item' to the 'Columns to pass through' section, and added 'Year1', 'Year2', and 'Year3' to the 'Columns to transform' section. I also renamed category column name to 'Year' (the default is 'Category'). You'll see how the transformed data will look in the 'Sample' grid at the bottom of the form. Now I have a Category column called 'Year' and a new column called 'Value' that holds the values that were pivoted out under the old Year1 etc. columns in your original pivoted data set.

I clicked OK on the Unpivot Data form, then OK on the Add Data Tables form. With my unpivoted data in Spotfire, I created a Line Chart with 'Year' on the X-axis, and Sum(Value) on the Y-axis.