0

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!

user2538414
  • 25
  • 2
  • 7

1 Answers1

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).

Apply Unpivot transformation on Add Data Tables form

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.

Unpivot Data transformation dialog window

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.

enter image description here

AmbivalentGeek
  • 173
  • 7
  • That's a great solution, thanks a lot! However I've got by this another problem. One of my columns changes it's name and values, when reloading data, because it can be change in the source excel file. Whenever the name of the column is changed,it cannot find original column anymore and doesn't get a new one. Is there any solution? – user2538414 Aug 01 '14 at 10:05
  • In my experience, the only solution is to ensure consistency in the source data so that Spotfire doesn't get tripped up when expected columns are absent. See if there's a way to put a placeholder row in the source data with a value of 0. Hope that helps you going in the right direction. – AmbivalentGeek Aug 11 '14 at 15:20