Tip of the Week

Most people are aware that you can sort bars by height in a Bar Chart.  However, the height is always determined by the total values for a given variable that is on the X-Axis. For example , we can sort a Bar Chart by height to show the sum of sales in each store location, so that the locations with more sales are shown on the left in descending order, assuming the Store Location column is on the X-Axis as shown below.

 If we wanted to add another dimension to this Bar Chart, we could color by gender, still keeping the same sort order.

 
What if we then wanted to sort by Males or by Females? There is no way to do this in a Bar Chart, but we can by using the Combination Chart.

Create a Combination Chart with the same x and y axis as in the Bar Chart, and then select to Series by the Gender column, keeping both Females and Males as Bars.  Then click on the Appearance tab in the Combination Chart's properties dialog and select to sort the x-axis by either Female or Male.

 
Below is the final output where the bars are sorted by total sales of Females per location.

 
Switching to sort by Males will update the graph accordingly.

 
 

Everyone knows about Spotfire’s powerful trellis features already. But there is a new feature in Spotfire 3.2 which allows you to export hidden trellis pages to PDF or PowerPoint, and even hidden table rows too.

Before we get to that, have a look at the screenshot below. If you are accustomed to creating a trellis using drag & drop, you may not know about the Pages setting which can only be accessed from the Properties dialog box.

 
If you use this Pages feature, you will see a display similar to the one below. Note the scroll bar which has been added on the right side of a simple line chart: this is best used when your trellis variable has so many values that they cannot fit on any screen-size comfortably.

So this is a great way to save space on the screen when you have a wide range of values in your trellis variable. But if you have used the Export to PowerPoint feature or, new in 3.2, Export to PDF, you may have wanted to show the hidden visualizations when you export.

To do this, go to File > Export and choose either PowerPoint or PDF. If you choose any option in the drop-down box at the top (except for ‘Active Page’ or ‘All Pages’, unless you choose those labelled ‘new page for each visualization’) then you will also be able to check the box highlighted in red, which will give you a new slide/page for every hidden trellis panel, and will also allow you to export hidden table rows.
 

By default, when you create an Analysis file, filtering is global and affects all the pages in the Analysis. This means if you filter something on one page, it will affect visualizations on other pages for the same data table.

In some cases, this is the desired effect, especially when you use one page to make selections and then you use a subsequent page to show details of those selections.  However, in many other cases, you may want to limit filtering to be specific to a page or some pages.  This can be accomplished by using multiple Filtering Schemes in your Analysis file. To turn on multiple Filtering Schemes, goto Edit > Document Properties, and then make the Filtering Schemes tab active.  On that tab, check the ‘Show filtering scheme menu in Filter Panel’ checkbox. It is on this tab, you can also create additional filtering schemes. 

Once you are done, you can click OK, and you will now see a new section in the top of each Filter Panel which allows you to select which Filtering Scheme you want to apply to the current Page.

You can create as many Filtering Schemes as you would like and then apply them to various pages as needed. 

When would this be useful? Anytime you had an analysis file but wanted to break down the analysis into various categories, and show the results per category.  For example, suppose you are creating an Analysis file for that will show data across 3 major regions (North America, EMEA, and APJ). You may want to setup the Analysis so that there are  a couple pages for all the data combined, then a couple pages specific to each region.  In this case, you setup a Filtering Scheme for each region and also for one global view of your data. Then, you select the appropriate Filtering Scheme for each page.  In each Filtering Scheme, you filter the data to only show the rows specific to either a region or one for the global view.   

While a topic like Filtering Schemes is a very powerful topic, it may not be the most intuitive to understand and locate without proper training. In fact, our Essentials training course for users covers many features and functionality which may go unnoticed inside TIBCO Spotfire Professional. Users who have been using Spotfire for years still find value and learn something new when attending these courses. See the following commercial to learn for yourself.

If you are interested in attending any of our Essentials training courses, using classroom or blended delivery, please visit our user pathway page to learn more about the course and its schedule.

By default Property Controls cannot be attached to a marking selection. However, by combining Script Controls with Property Controls, we can create the desired functionality.

The first step is to create the Property Control(s). There are a variety of different ways to do this logic. First, you can use list box controls to show unique values in a column, and then mark the selected values. Or you can have one drop down to allow the user to select a column, and then a value to search for, and mark all rows where the specified column has the specified value.  We can even add multiple Property Controls to allow more complex logic (like if Col A = 3 and col B = ‘Boston’) and we can also use logical operators, like less than and greater than.

Assume our data table includes information about various cars, including make, model, sticker price, engine size, fuel economy, etc….  We want to create a Property Control that allows a user to specify a minimum sticker price. For this we can use an Input field control type, and have the control attach to a Document Property of type real (since the sticker price column, MSRP, is also a real).

We then need to create a Script Control which will mark the rows where the sticker price is greater than or equal to the value specified in the Property Control.  The Control will read in the minVal property created earlier as an input parameter.

The entire script is below:

from Spotfire.Dxp.Data import IndexSet
from Spotfire.Dxp.Data import RowSelection
from Spotfire.Dxp.Data import DataValueCursor
from Spotfire.Dxp.Data import DataSelection

rowCount = Document.ActiveDataTableReference.RowCount
rowsToInclude = IndexSet(rowCount,True)
rowsToMark = IndexSet(rowCount,False)

cursor1 = DataValueCursor.CreateFormatted(Document.ActiveDataTableReference.Columns["MSRP"])


for  row in  Document.ActiveDataTableReference.GetRows(rowsToInclude,cursor1):
   rowIndex = row.Index
   value1 = cursor1.CurrentValue
   if float(value1) >= float(minVal):
              rowsToMark.AddIndex(rowIndex)

Document.ActiveMarkingSelectionReference.SetSelection(RowSelection(rowsToMark), Document.ActiveDataTableReference)

When complete the functionality will look something like below:

.

 
Remember that using this capability is not limited to a single column, so you can add in as much complexity into your logic a you want.

If you are interested in learning more about the script listed above, or how to accomplish similar functionality, please consider taking our Script Controls training course.

We have also just added a new section on our Community called Analytics Exchange where community members can post a variety of assets, including Script Controls, Expressions, Analysis Files shared via the Web, S+ and R Scripts and Data Functions, and Sample Data Sets.  I am including the script above in the Script Controls section. Please remember to check out the Exchange to search for and find useful content, and also please feel free to post content you have created which you wish to share with others.

It is very important to add instructions in your Analysis files in order to explain to others how they should use it.  This can be done in a variety of ways. The most direct way is to add Text Areas that contain the necessary information.  The downside of this approach is it takes up a lot of real estate in the analysis file.  Another viable approach would be to add a button which will launch the instructions in a popup dialog.  We will first show you how to do this in TIBCO Spotfire Professional, and then in a future post we will show you how to do this so it works in TIBCO Spotfire Web Player.

Once we are done, you will have a small Text Area with just one button. When the user clicks on that button, a window will open above Spotfire with the instructions. This can be developed to be one instruction set for the entire analysis or one for each page. See the image below for what the final version could look like.

 

To start with, we need to figure out where the instructions will be stored. Given this is meant for people only using Spotfire Professional and not the Spotfire Web Player (we will discuss how to do this for a Web Player file in an upcoming post), we need to be consider a user may be working offline and not connected to the internet or the Spotfire server. If this is not a concern, you can code the instructions in an HTML page and post them on a web server.  

Then we need to launch a .NET windows.form which contains a Web Browser. The following code will launch a Web Browser in a windows.form control and display www.server.com/instructions.html inside it.

import clr
clr.AddReference("System.Windows.Forms")
from System.Windows.Forms import Form, DockStyle, WebBrowser

f = Form()
f.Text = 'Instructions'
f.Width = 800
f.Height = 600

wb = WebBrowser()
wb.Navigate("http://www.myserver.com/instructions.html")

# Layout
wb.Dock = DockStyle.Fill
f.Controls.Add(wb)

# Display the Control

f.ShowDialog()

Let’s assume you cannot simply point to a file on a web server and you would like to keep the intstructions directly inside the Analysis File.    The best way to do this would be to write the instructions to a string, which is then stored in a temporary file, and referenced in the WebBrowser control.


import clr
clr.AddReference("System.Windows.Forms")
from System.IO import Path, File
from System.Text import StringBuilder
from System.Windows.Forms import Form, DockStyle, WebBrowser

sb = StringBuilder()
sb.Append("<h1>Instructions for this Analysis</h1>")
sb.Append("<p>Here you need to check to see if blah blah blah</p>")
sb.Append("<p>After that you need to check to see if blah blah blah</p>")

tempfilename = Path.GetTempFileName()
newHTMLFile = Path.ChangeExtension(tempfilename, ".html")
tempfile = open(newHTMLFile, 'w+b')
tempfile.write(sb.ToString())
tempfile.close()

f = Form()
f.Text = 'Instructions'
f.Width = 800
f.Height = 600

wb = WebBrowser()
wb.Navigate(newHTMLFile)

# Layout
wb.Dock = DockStyle.Fill
f.Controls.Add(wb)

# Display the Control
f.ShowDialog()

A third option would be to list the instructions in a Document Property rather than as a string inside the Script.  To do this, you can create a Document Property and then store the instructions in that. The value of the property is limited, but there is more characters allowed in the Description, so you can use that field to enter the instructions.

 

Then in your script, instead of building a string to pass in the instructions to the Web Browser, we can reference the description of this property and pass that in. Assuming our property is called page1Instructions, we can use the following code to reference the Description of that property.

strInstructions = Document.Data.Properties.GetProperty(DataPropertyClass.Document, "page1Instructions").Description

The complete script for this third option is shown below:

import clr
clr.AddReference("System.Windows.Forms")
from System.IO import Path, File
from System.Windows.Forms import Form, DockStyle, WebBrowser
from Spotfire.Dxp.Data import DataPropertyClass

strInstructions = Document.Data.Properties.GetProperty(DataPropertyClass.Document, "page1Instructions").Description

tempfilename = Path.GetTempFileName()
newHTMLFile = Path.ChangeExtension(tempfilename, ".html")
tempfile = open(newHTMLFile, 'w+b')
tempfile.write(strInstructions)
tempfile.close()

f = Form()
f.Text = 'Instructions'
f.Width = 800
f.Height = 600

wb = WebBrowser()
wb.Navigate(newHTMLFile)

# Layout
wb.Dock = DockStyle.Fill
f.Controls.Add(wb)

# Display the Control
f.ShowDialog()

#Must delete the temp files so they do not persist on the users’ computer
File.Delete(newHTMLFile)
File.Delete(tempfilename)       

Yet another solution would be to add a description into each Visualization using its Properties dialog. Then have the Script loop through all Visualization’s on the page and append its Title and Description using the StringBuilder.

Remember, this solution only works in Spotfire Professional and Spotfire Enterprise Player, it will not work in Spotfire Web Player since it’s a .NET windows control we are displaying.  We will show you in an upcoming post how to create similar instructional dialogs for your files that you will share over the Web Player. To learn more about Script Controls, consider taking SP232: Automation APIs using IronPython

Suppose you use Spotfire to create a dashboard showing your customer's purchases every quarter. With every updated dashboard, you may want to see which customers are new in your top 10, which customers have dropped out of your top 10, and which customers continue to remain in your top 10.

To start, you need to create a list from the top 10 customers.  In your dashboard inside TIBCO Spotfire Professional, mark the top 10 and then right click on a visualization and select “New List from Marked. . .”

Then, when next quarter’s dashboard is ready, you can create a new list with the Top 10 Customers from that following the same steps as above:

We can then analyze any of our top 10 lists using the List Logic capabilities.  For example, if we want to see who was in our top 10 in Q2 but not in Q1 we can by creating a new list which contains the customers from the Top 10 Q2 list who are not in the Top 10 Q1 list.

There are many times when you want to use a Property Control to display a list of Columns, but only show a subset of Columns rather than all of them.  This could be because only certain columns are relevant for your analysis, or it could be because you have so many columns, it would be unusable to display them all.  If you want to limit columns based off what is relevant for your analysis, you could use something like the datatype and choose to display only columns with a given data type.

You can do this when you create the Property Control by specifying the datatype you want to limit to in the ‘Selectable columns’ field shown below.

In this case, we are choosing to only show columns where the datatype is a string.

What if you wanted to allow the consumer to define what to show in the property control?  We can do this with a combination of Property Controls and Script Controls.  This can be very useful if you have a Data Table with too many columns to show in a Property Control.

First, we create an Input field Property Control:

 
This Property Control is going to let a consumer enter a search string to use for selecting what Columns to display.  

Then we create a multi-select List Box Property Control. This will be the Property Control which displays all the Columns matching our search criteria. This Property Control  allows us to use properties to limit the values shown in the list,  as we saw earlier when we limited by showing only String Columns. So, we will create a new property called isIncluded, and only show Columns where isIncluded is true.  This is done by adding isIncluded:True into the ‘Selectable columns (limit through expression)’ field:

We now need to create the isIncluded property. This can be created in a variety of places, but the easiest may be to go to Edit > Column Properties and create it there.
 

Then we add a Script Control. The Script Control will read the search criteria from the input Property Control, use it to find matching Columns in the Data Table, and then set the isIncluded property on those Columns to True.

The Script Control will take in the value from the input Property Control as the and call is pattern. It will then loop through each Column and use the Python regular expression object to search for a match with the Column name and the pattern. If there is a match, the isIncluded property is set to True, otherwise, False.

The End Result

Entering a search expression in the input field and then clicking the 'Filter Columns' button will display only matching Columns in the list box Property Control.  For the search expression , use the pipe character (|), to add multiple search criteria. Other syntax follows Python regular expression syntax.

To learn more about Property Controls, please take either our Authoring Bootcamp  or our 3.1 Delta Training course. To learn more about Script Controls, please take either our Developer Bootcamp or our Automation APIs using IronPython course.

Also, remember to sign up for our free Analytics in Action webinar series, run monthly.

Using Script Controls can really increase the analytic power of your Spotfire files and make it easier for people to search for insight.  Since an entire script is wrapped in a single transaction, tasks like looping through filters will only update the document once at the end.  This means, to perform tasks where you want to update the document multiple times, the users must execute the script multiple times.  One problem with this is that scripts do not maintain state between uses.  To solve this , we will use a property to store our state.

Consider the following example.  Assume we are analyzing Unemployment rates by County in the United States from 1999 – 2009. We can display this on a Map Chart as shown below.

 

This is nice and very useful to visualize, but a consumer may not want to see the unemployment rates summed up for all 11 years, they may want to see how the rates change year from year instead.  Sure, they can use the filter panel to move the year filter one year at a time, but it would be nice to wrap this in a Script so the consumer just has to click a button. At the same time, we can also perform some helpful updates while we are in there.

 

To start, create a Script Control in a Text Area.   The Script is going to pass in a visualization parameter that refers to the Map Chart.

The script will retrieve the ‘Year’ Column Filter as an Item Filter and will move the Item Filter by one step. We need to use a property to store the current step so we can just increment it by one next time the Script is executed.

 

Below is the full script:

 

import Spotfire.Dxp.Application.Filters as filters

from Spotfire.Dxp.Application.Filters import ItemFilter, FilterTypeIdentifiers

from Spotfire.Dxp.Data import DataProperty, DataType, DataPropertyAttributes, DataPropertyClass

 

myPanel = Document.ActivePageReference.FilterPanel

myFilter= myPanel.TableGroups[0].GetFilter("Year")

 

myFilter.FilterReference.TypeId = FilterTypeIdentifiers.ItemFilter

itemFilter = myFilter.FilterReference.As[filters.ItemFilter]()

 

whichCol = itemFilter.DataColumnReference

if (whichCol.Properties.PropertyExists("CurrentStep") == False):

    myProp = DataProperty.CreateCustomPrototype("CurrentStep",0,DataType.Integer,DataPropertyAttributes.IsVisible|DataPropertyAttributes.IsEditable)

    Document.Data.Properties.AddProperty(DataPropertyClass.Column, myProp)

    whichCol.Properties.SetProperty("CurrentStep",0)

else:

    whichVal = whichCol.Properties.GetProperty("CurrentStep")

    print whichVal

    print itemFilter.Values.Count

    if (whichVal == itemFilter.Values.Count):

            whichCol.Properties.SetProperty("CurrentStep",0)

    else:

        itemFilter.Value = itemFilter.Values.Item[whichVal]

        if (itemFilter.Value):

                        vis.Title = "Overall Unemployment by Country in " + itemFilter.Value

        else:

                        vis.Title = "Overall Unemployment by Country from 1999 to 2009"      

        whichCol.Properties.SetProperty("CurrentStep",whichVal+1)

 

 

The end result? Consumers can click on the button and it will step through the Year filter so that they can see the unemployment rates for the given year one year at a time. In addition the title of the Map Chart is updated to include the year for the current step.

 

Below is the result of the visualization and Filter Panel when the Script is executed to the point where the year column is 2007.

 

 

For more information on Script Controls, consider taking our  blended training course dedicated to them.

One of the new features of TIBCO Spotfire version 3.2 is called Lists.  Many of you who have been around since the DecisionSite days will find the functionality very similar to the Portfolio tool, with a few key additions. In an earlier post,  we discussed various methods for adding categories into our analysis.   With Lists, you have an additional method, which is very similar with tags, but also different in the following ways:

•    You can add annotations, or knowledge,   on the items in your List to save and share the insights
•    Your List exists in a separate file outside of your document and can be applied across many analysis sessions
•    You can use some logical operations to easily search for and compare items in a List

The best way to explain the capabilities above is with an example. Let’s assume we are looking at sales from a chain of stores.  We mayhave  identified during our analysis a couple of customers who we want to ‘watch’, either because they are big spenders and we want to make sure they continue to keep purchasing items, or maybe they return a lot of items, and  we want to monitor them moving forward.  Or maybe they are regular purchasers of a given brand.

Just like with Tags, we can create a new list, called ‘Customers to Watch’, and can include the customers that fit into our criteria mentioned above.  The easiest way to accomplish this is by marking the customers and then selecting the ‘New List from Marked…’ command.

When you have done this, you will see the Lists panel is updated with your new List.

 

 
This List can then be exported into a TIBCO Spotfire Lists file (.lists).  The benefit of this is it can be applied to other documents you have which also contain information about customers.  

 
Adding Knowledge into your Analysis
In addition to the ability to use the Lists to classify your data, you can also add additional knowledge about your analysis. This concept is called Annotations. Let’s say we are interested in watching one of our customers more closely as a potential person to be our new spokesperson.  We can find the customer in the List and choose to add an annotation from the ‘List Item Properties’ dialog.

When the analysis is opened later or when the List is used with another analysis file, we can search for the annotations and find the necessary customer id.  

 
List Logic Operations
In a future post, we will discuss the list logic capabilities for Lists, and walk through an example of how you can classify customers into different purchasing categories and then use the list logic capabilities to compare customers who fall into a specific purchasing habit (for example, those who purchase Brand A but never purchase Brand B or C).

In an earlier post, we discussed how to add Horizontal Bar Charts and other charts into TIBCO Spotfire using S+ or R.  In this related post, we will show you how to add Horizontal Bar Charts and other charts into TIBCO Spotfire using Google Charts.

Google Charts is a simple yet extensive way to create a Web based chart. Google creates a PNG image of the chart, typically from data and formatting parameters in an HTTP request, although it also supports making the request in a SRC attribute of an image tag.

Using a Text Area and Script Controls in TIBCO Spotfire, we can access the necessary data and send it to Google, to render one of the many chart types they have.

Assume we have a data set that contains sales data for each salesperson.  We can gather the sum of sales data for each salesperson , using a Script Control, and can send it into the SRC attribute for the Image, like so:
<IMG src="http://chart.apis.google.com/chart?cht=bhg&chs=550x400&chd=t:1431,1085,1393,345,2514,2128&chxt=x,y&chxl=1:|Alexander|Amy|Barry|Brenda|Collin|Dwayne|&chxr=0,2600,20&chds=0,2600&chco=4D89F9&chbh=35,0,15&chg=8.33,0,5,5">

In the same script , we can then launch a new TextArea and set the content to be the image referenced above:

from Spotfire.Dxp.Application.Visuals import HtmlTextArea
from Spotfire.Dxp.Application.Visuals import VisualTypeIdentifiers
from System.Text import StringBuilder

sb = StringBuilder()
sb.Append('<IMG src="http://chart.apis.google.com/chart?cht=bhg&chs=550x400&chd=t:1431,1085,1393,345,2514,2128&chxt=x,y&chxl=1:|Alexander|Amy|Barry|Brenda|Collin|Dwayne|&chxr=0,2600,20&chds=0,2600&chco=4D89F9&chbh=35,0,15&chg=8.33,0,5,5">')

textArea = Document.ActivePageReference.Visuals.AddNew[HtmlTextArea]()
textArea.AutoConfigure()
textArea.HtmlContent = sb.ToString()

Google Charts have many chart types. You can even modify the horizontal bar chart to act as a Gantt Chart.

 
One limitation of this approach where we use the SRC attribute to specify the parameters is that there is a maximum length for the URL used.  This limitation is browser dependant but typically is around 2000 characters.

If you run into this, you will have to create the Google Chart using an HTTP Post instead of an HTTP GET (the URL method). This can be accomplished, but does require the SDK. Inside the SDK, there is a sample visualization called Web Details, which actually embeds a web page inside Spotfire. You can configure the Web Details to use HTTP POST to display the same images as shown above..

TIBCO Spotfire Professional provides a variety of ways to add categories into your data above those that are included in the raw data table. Four methods we will discuss are Columns from Calculations, Tagging, Columns from Binning, and Filter Out/To.

Tagging and Calculated Columns can both be used to create additional categories in your data. For example, I can create a tag to place rows into a new category with 3 values; low, normal, and high. The benefit of this is I now have a new filter and column created inside my data table that I can use to color by, size by, and perform other operations on.

In the example below, we create a new category in our data table called ‘Utilization Type’ by tagging.

 

Similarly, I can create either a calculated column or custom expression to do the same using either an if statement or a case statement (we will see an example in a bit).

Which one should I use? Well it depends on your data and what your purpose is.

Calculated Columns

If you plan on replacing data, and you can easily define your ‘categories’ using an algorithm, then I would choose to use a calculated column or a custom expression. Calculated columns and expressions both get re-calculated by default when the original data table is replaced automatically. This makes this option more efficient, so that you do not have to repeat performing the same tagging sequence.

The following is an example of an expression created to do the same things as the tagging above:

case when ([Utilization Type])=("Vacation") then "Vacation" when ([Utilization Type])=("Production Task") then "Production Task" when ([Utilization Type])=("Self Dev Task") then "Self Dev Task" when ([Utilization Type])=("Travel") then "Travel" when ([Utilization Type])=("Support") then "Support" when ([Utilization Type])=("Holiday") then "Holiday" else "Teaching" end

NOTE: You will need to remember if you add more values in the category, that you will need to update your expression, otherwise it will be incorrect and new values will be added to the ‘Teaching’ value as that is after the else statement in the expression.

Tagging

Tagging is commonly used when you need to place your data into additional categories, that may not be easily computed using an algorithm. For example, you may be running through your analysis of stock data and you want to tag records as ‘buy, ‘hold’, and ‘sell’. Unlike the Utilization Type, your analysis for putting records into ‘buy’, ‘sell’, and ‘hold’ is more complex and involves some ad-hoc analysis, so tagging is the most efficient method. Since this analysis is used in more of an ad-hoc fashion, the tags are saved with the analysis but not automatically re-applied when new data is replaced or reloaded.

You can still enable them to work when you reload data, but you need to specify a key columns to uniquely identify the records. This topic is described at the following URL:

http://stn.spotfire.com/spotfire_client_help/data/data_details_on_select_key_columns.htm

Binned Columns

Similar to Calculated Columns, you can also define new categories by using the New Column by Binning dialog. (available from Insert > Binned Column…). From this dialog, you can add a new column that places your data into predefined bins (categories), using a variety of methods.



Methods include bins by specific limits, even intervals, even distribution of unique values, standard deviations, and substrings. The type of methods available are dependent on the data type of the source column.

This capability is typically used when you have a specific algorithm you wish to apply to create your categories, but it is more advanced than simple if or case statements as shown in the earlier calculated column example. It is also very useful when you want to take continuous data and convert it to categorical data to perform further analysis.

NOTE: You can still create bins using the expression language in a calculated column (see image below), but the Insert Binned Column dialog is an easier to use interface rather than entering the code manually.

Filter to and Filter out

Finally, if you are trying to create a column that contains only two categories, it is possible to do this by first marking the records of interest, and then right-clicking , and selecting ‘Marked Rows > Filter To’ or ‘Marked Rows > Filter Out’.

Both options will create a new Filter. The first one will filter out every row except the ones marked, and the second one will filter out the marked rows only.

This functionality can be used in a variety of ad-hoc analysis techniques, including removing outliers or other data points to see how the rest of the analysis changes. It can also be used, as we mentioned earlier, to create a column with 2 categories. The default name of the filter can be changed to something more descriptive by right-clicking in the filter and selecting ‘rename’ or by going to ‘Edit > Column Properties’.

What to extend it even more?

While the new columns are stored with the analysis file, you may want to persist this information outside the file so that it can be leveraged in other applications and other analysis files. One such solution is to use a combination of Information Services and our SDK to create a customized tool that will write back the new columns into the source database. Information Services can be setup on the Spotfire Server to run stored procedures, so you can setup a stored procedure which accepts values and performs an insert into the database. You would then need to use the SDK to create a tool , which the user can launch to specify the column(s) they wish to write back. The tool would grab the columns, and pass them into the stored procedure as input parameters. Alternately , you could use a Script Control inside a specific Spotfire file rather than writing a Custom Tool.

Summary

To summarize, there are a variety of techniques for creating additional categories in your data table. While many of them can be interchanged, each has its own strength and weakness depending on the scope of the analysis (a quick ad hoc analysis or a persistent guided analysis), the number of categories required, and the algorithms used to create the categories.

Any additional thoughts or information on creating categories, reply to this post with your thoughts.

Ever want to use a  Horizontal Bar Chart in TIBCO Spotfire?  Sure, you can use the SDK to build a custom visualization, but that is alot of overhead.  With the ability to execute S+ and R from within the Spotfire platform, there is an easier way to accomplish this capability: using Data Functions. Ideally this should be setup as a detailed visualizations, but can be setup to be main visualizations as well.

First, you will need to register a Data Function using Tools > Register Data Functions from within TIBCO Spotfire Professional.  The Data Function will be an S+ Script , which will generate a Horizontal Bar Chart and output it as a png image. The script is shown below.

 
The script will take in two input parameters. Both are required and will be of type Column.  These will be the columns we want to display on the X and Y axis of the Horizontal Bar Chart.

The script will output one parameter. It will be of type Value. This will be the Bar Chart , being returned as a PNG image.

Once we have created the S+ Script, we now can include it in a given Spotfire Analysis File using the Data Functions tool from Tools > Data Functions.  Here, we must map the expected input parameters to values from the analysis document.
In our case, the current analysis document has the following columns:

 
We would like to have the Bar Chart display the ‘Total Amount of Purchases’ for each ‘Store Location’.  To generate this , we need to send in both of those columns as the input to the script.  First, we send in the Store Location column. Since we want to have this be a details visualization, we select to only send in marked rows to the script.

 
Next, we send in the ‘Total Amount of Purchases’ column. However, this is a currency column in Spotfire and S+ does not support currency data types. To resolve this, we define the input as an expression instead of a column, and then create an expression that casts the ‘Total Amount of Purchases’ column to an integer.

For the output of the script, we want to attach the image being returned to a document property of type Binary.

 
Lastly we need to create a Property Control from within a Text Area to display the image.  For this we will pick a Label Property Type.

Once done, we see the finished product.  Mark records in the Scatter Plot and then a Horizontal Bar Chart will be displayed as a Details Visualization.

Everytime you update the marking, the Data Function will be re-executed and an updated Horizontal Bar Chart will be displayed. This works since we checked the 'Refresh function automatically' button.

Interested in learning more about Data Functions? Take our course specific for learning Data Functions for either S+ or R: TIBCO Spotfire Data Functions : Executing S+ and R code from TIBCO Spotfire. The course can be taken using our blended learning model for only $600 per person. Blended training gives you access to our online training portal to consume asynchronous materials including for 30 days. During those 30 days, you have access to an instructor to help answer any questions you may have.  When the 30 days are up, there is a live webinar typically lasting 2 hours to review concepts, do live demos, and also to answer any questions.  This model is well suited for adult learners as you can break up the learning over time to retain more and also have it fit into a busy schedule.

Last week we discussed how to create cascading Property Control Drop downs using Data On-Demand.  This is done using Information Links. If you do not use Information Links, this week’s tip will show you how to accomplish this without using Information Links. All the data will be loaded into Spotfire at once, but you can use the drop downs to select what information to show in given visualizations.

To do this we make use of Calculated Columns.  Let’s assume we have the same Data Table from the previous tip, which contains ‘Region’, ‘State’, and ‘City’, along with other Columns, and we want to allow the user to select which Region to load via a Property Control, and then display States from that region in a second Property Control.  Finally after selecting a State from the second Property Control, a visualization is updated to display Towns and Cities from the selected State.

First step is to create a drop-down list Property Control to display unique values from the Region column.

Next step is to create a Calculated Column called ‘Filtered State’. In the expression we should check if the Region Column value equals the value specified in the property we created earlier. If it does, it will output the value from the State Column.

if ([Region] = "${whichRegion}",[State],null)

Next step is to create the second Property Control drop-down. This one will display unique values in the newly created Filtered State Column.
 

The final step is to create a second Calculated Column called ‘Filtered City’. In the expression we should check if the State Column value equals the value specified in the property we just created. If it does, it will output the value from the City Column.

if ([State] = "${whichState}",[City],null)

Now we can create any visualization and use the ‘Filtered City’ column to display the Cities and Towns from the selected State from the selected Region.
 

Interested in learning more, take our 3.1 Delta training or our end user training courses either onsite, regionally, or using our blended online/webinar model.

One of the nice features of TIBCO Spotfire 3.1 is the enhanced ability to load data on-demand.   In previous versions, when loading data from an Information Link, you could prompt a user to select specific values to load. The downside of this is when you wanted to change those selected values, you needed to reload the Information Link. This would cause your document to close and re-open, losing any updates you made to the state of your document.

Starting in version 3.1, you can create an On-Demand Data Table , which can be used to let the user select what to load. The user can change their minds and load something else later on, keeping the document open and the state of the document unaffected. This makes it much easier for users to drill down and look at various characteristics of the data, without having to load the entire data table into memory initially.  In this case, you load the data only when you need it.

To do this, first you will need to create an Information Link which only contains the Column which you want to filter on first. For example, if we have a Data Table which contains ‘Region’, ‘State’, and ‘City’, along with other Columns, and we want to allow the user to select which region to load, you would create an Information Link which only contains the ‘Region’ column.

To do this, inside the Information Designer, create an Information Link which just contains the ‘Region’ column, and then specify to use ‘Distinct’ conditioning.

While you are in the Information Designer, you should create another Information Link which will include all the columns you want to load for your analysis. No conditioning should be applied.

Now that you created the Information Links, from within Spotfire Professional, choose File  > ‘Open From > Library…’ and select the first Information Link you created (which just includes the ‘Region’ column). This should load a data table with just one column and just the unique values in that column:

Now we will create a Text Area and add a Drop-down list Property Control into it.

In the resulting ‘Property Control’ dialog, create a new Document Property then for the ‘set Property value through’ option, select ‘ Unique values in column’. Choose the ‘Region’ column and make sure you check to Include None as an option.
 

We now have a working dropdown which will allow the user to select one of the regions.

Next, we need to load the full data table. For this, choose File > Add On-Demand Data Table and select the second Information Link you created which includes all the columns you want in your analysis. Make sure you check the ‘Load automatically’ checkbox.

 
From within the same “Add On-Demand Data Table” dialog, highlight the ‘Region’ column and click the ‘Define Input…’ button.

On the resulting ‘Define Input’ dialog, select ‘Values (fixed/properties/expression)’ as the Input type and in the Settings section, select ‘Property’ and select the Document Property you created earlier.
 

Now, create more of your analysis that you would like to see. For example, for my data table, I created a Bar Chart which show the sum of my sales change from Q3 to Q4 (which is a column in my data set) on y-axis and City on the x-axis. Since we have not loaded the full data table yet, the chart will be empty:

Once you select a region, an Information Link will go and retrieve the data specific for that region and then populate the current document and its visualizations. You can later switch the value in the drop down and the same Information Link will be executed, but this time, it will return only values for the newly selected property value.

Using the same concepts, you can add a second Drop-down list Property Control (or even a third and a fourth), which will act as sequential drop-downs. For example, in the image below, we have added another Drop-down list Property Control to control which ‘State’ to select. ‘State’ is another column in our data table.  The State Property Control will update to show only the values appropriate for the selected Region.

The new On-Demand data loading in 3.1 adds a variety of nice features shown above. First, it can be used to allow the user to specify what data to load into the analysis, and the user can change the values later on, without reloading the document. Second, you can create core visualizations that are not details visualizations (which was the case in the data on demand feature before Spotfire 3.1).

Don’t use Information Links currently? In next week’s post we will recreate the cascading drop downs without using Information Links. All the data will be loaded into Spotfire, but you can use the drop downs to select what information to show in given visualizations.

There are many times a user may only want to see one specific plot at a given point in time. In this scenario, you can create all the different types of plots a user may want to see and link them from a cover page using bookmarks.

However, it takes a lot of real estate to have all visualizations in a file. One option would be to use Property Controls and expressions to dynamically configure/create the visualization when the user needs it.

Assume the consumer may want to look at a cumulative sum, a 4 period rolling average, or a year-to-date rolling average.

For all these plots the x-axis would be the same, possibly a Date hierarchy for example.

We can then create a drop-down list Property Control where the drop down values are set via expression.

Then for each value we can enter the expression we want to appear on the Y-axis as well as a user friendly display name to be used in the property dropdown list. The screen shot below depicts a Cumulative Sum expression being used.

Once you are done adding all the values you want, you then need to attach the property control to the y-axis expression as shown below.

The end result is a drop down list which changes the Bar Chart to display either a Cumulative Sum, 4 period average, or a YTD rolling.