Spotfire's Text Area - How to Control a Visualization Axis Through a UI Element

When creating an Analysis to be consumed by others it is a good idea to provide User Interface (UI) elements to control the exploration of the data beyond or even replacing those that appear by default in Spotfire; this is sometimes referred to as Guided Analytic.

For example, if your Analysis is aimed at people without a solid understanding of the underlying data you might find useful to actually hide Spotfire's filters, axis selectors or other features that could prove "too much powerful" for your intended target.

In this post we will show you how to create an Analysis like that of Figure 01 where you modify a visualization axis using a UI control embedded in a Text Area; if the user selects Total Sales, Total Units Sold or Average Unit Price from the drop down list in the top of the page the Bar Chart at the bottom will change accordingly.

Figure 01 - Sample Guided Analytic

So, let's assume you have a Table with the amount (Store Sales) and quantity (Unit Sales) sold by different Brands (brand_name) and you have create a very simple Bar Chart (see Figure 02).

Figure 02 - Initial Situation

First we need to create a Text Area to hold the UI control,add some descriptive text and then Insert a Drop Down List Property Control (see Figure 03).

Figure 03 - Insert Drop Down List Property Control into the Text Area

Since the Drop Down list has to be associated with a Document Property Spotfire asks you for it; we are going to create a new one just for this example so we click on New and we fill the New Property dialog that pops up (see Figure 04) and click OK.

Figure 04 - Create New Document Property

Now comes the first key point of the whole process... somehow we must specify which values will appear in the Drop Down List and Spotfire offers plenty of options but we are going to take the fast lane and select Fixed values at the Set property value through: prompt. Don't be afraid we will revisit this topic in the future and explain the other choices.

With Fixed values selected the bottom part of the dialog now shows a list where we can just type the Dipslay Name and Value pairs that will fill our Drop Down List. The Display Name is what the user sees in the UI Control while the Value is the actual text that will be stored in the associated Document Property.

We type Total Sales and Sum([store_sales]) in the first row, Total Units Sold and Sum([unit_sales]) in the second row and finally Average Unit Price and Avg([store_sales]/[unit_sales]) in the third, like displayed in Figure 05.

Figure 05 - Fixed Values for ValueToAnalyze Property

You probably have guessed what's going on here: the first value in each row (Total Sales, Total Units Sold, Average Unit Price) is the human readable label the user will select in the Drop Down and the second value is an Expression telling how to calculate that concept.

After clicking OK and exiting the Text Area edit mode you will end up with a pretty useless drop down list because even if you can select any of the Display Names you entered on the previous dialog nothing happens on the bottom Bar Chart!

But don't be afraid, connecting the Bar Chart with the Document Property is the second key point of the process and we will fix it right now.

Open the Bar Chart Properties dialog, select the Value Axis option, right click where your current Axis expression is - Sum([store_sales]) in the example - and choose Custom Expression...

Figure 06 - Setting a Custom Expression for the Value Axis

When the Custom Expression dialog appears clear whatever text is on the Expression field and then search the middle column on the top (Available properties for column:) for the Document Property you have just created.

Select the Document Property and click Insert Properties, now your Custom Expression dialog should look like Figure 07.

Figure 07 - Custom Expression Referencing the Document Property

Finally click OK to close the Custom Expression dialog, close the Bar Chart Properties dialog and your drop down list should be now fully functional.

If you compare your Analysis with the samples at the beginning of this post you might notice some cosmetic differences like updating the visualization title and axis label when the user selects a new value from the drop down list... that is easily achieved but this post is long enough as it is, so we will leave it for another tip.

Spotfire's Unpivot Transformation - Sample Video

This video shows how to use the Unpivot Transformation to convert column data into rows which can prove useful when working with spreadsheets and other similar sources.

Spotfire's Unpivot Transformation - Converting Columns Into Rows

When analyzing data - particularly spreadsheet files - we frequently face information that has been organized in columns when it would be a lot more useful if broken down in rows.

Take for example the spreadsheet shown in Figure 1 where each row represents a different store and columns B to I contain the annual sales figures from 1998 to 2005. This kind of organization is good to look at tabular form but poses some problems when creating graphical visualizations.

Figure 1 - Store Sales per Year

If we import the Store Sales per Year spreadsheet into Spotfire without any further transformation we obtain a very similar organization (Figure 2) but what we want is to break down each row into eight new ones: one row with the store sales for 1998, one row with the store sales for 1999, and so on; and then is when Spotfire's Unpivot Transformation comes handy!

Figure 2 - Data Loaded into Spotfire

Now using Insert -> Transformation a dialog will popup where we must select the appropriate table and transformation (Unpivot) and click on the Add button which will display the Unpivot Data dialog (Figure 3).

Figure 3 - Configured Unpivot Transformation

Any column that should remain unchanged must be added to the Columns to pass through list as Store ID in our example. For every column added to the Columns to transform list Spotfire will generate a different row that has the column's name and value stored in the Category and Value columns.

Coming back to the Store Sales per Year spreadsheet when we added the 1998 column to the Columns to transform list Spotfire created a row for each store with the value 1998 in the Category column and the corresponding sales figure in the Value column.

Usually it makes sense to give more meaningful names to the Category and Value columns which were renamed Year and Sales in Figure 3.

After confirming everything is configured as desired we click OK a couple of times to close all dialogs and execute the transformation giving the result depicted in Figure 4.

Figure 4 - Transformed Table

And that's all for today's tips and tricks, I hope you find it useful.