February 2010 - Posts
-
I’d like to be one of the first to start introducing you to the amazing new features of TIBCO Spotfire Professional 3.1. This will kick off an arc of tips related to 3.1 features. This tip will introduce you to the updated Property capabilities.
If you remember our previous tip discussing Column from Marked, you will remember that one use of it was a workaround to allow users of the Web Player to update axis in visualizations. You may also remember an earlier tip on using Global Variables inside TIBCO Spotfire , where we discussed creating a document property that held the exchange rate, which could then be used in various visualizations in place of hard coded values.
What if you wanted to allow consumers who use the web player or any client to update the property themselves? This would allow consumers to update the expressions included in many visualizations, creating a kind of scenario analysis.
Let’s assume we have a Bar Chart that shows the initial amount of an investment for three separate investments (100, 200, and 300).
We can then create a new property, just as we could have before version 3.1. If we want to create a Document Property , we can do that from the Edit > Document Properties. In this case, we will create a property called rInterestRate. It will be a Real and the starting value will be 0.05. There are new ways to create these properties as well in the UI for 3.1, but we will not be discussing those in this tip.
Now that we have the property created, we need to include the properties into our logic for our analysis file. Let’s assume we want to create a second Bar Chart, and this one will show amount of the three investments after 10 years using a user specified interest rate. This user specified interest rate is going to be stored in the property we just created.
In this example, we will right-click on the Y-Axis of the second Bar Chart and select Custom Expression…
In the resulting Custom Expression dialog, there is new support to enter a property into the expression. There was a method for doing this before 3.1, which is still supported, but there is a new and improved Custom Expression dialog which shows all the available properties as a pane. This will allow you to highlight the property and click the Insert Property button to add the property into your expression.
The specific expression we need to use to calculate the total of your investment over 10 years is the following:
Sum([Amount]) * Power(1 + <<interestRate>>,10)
where <<interestRate>> needs to be replaced with the interest rate property.
If you use this method, you will notice a new syntax, ${propertyName}, has been used to add your property into the expression .This is a preprocessor , which will preprocess the variable and replace it with the value back into the expression. This allows you to use the precocessor syntax in other locations of the expression, if you are familiar with our expression language, included in the value for the As keyword.
You can now see the second Bar Chart displaying the total of each of the 3 initial investment amounts over 10 days using the default 0.05 interest rate.
The final step is to now expose the rInterestRate property to Business Users so they can update the value themselves, thus updating the expression and the second Bar Chart.
To do this, we can use the 3.1 Property Controls (new in version 3.1) to expose a property in a form control, which can be set. This is supported in the Text Area. When in edit mode in the Text Area, click on the Property Control button, as shown below and select one of the form controls to use. Assume we want our interest rate to go from 0.0 to .10, we could use a list box, a drop-down list, or a Slider. A slider is probably most appropriate so we will select that.
When defining the property controls in the resulting dialog, we hook these controls up to a specific property. In this example, we will attach the control to the rInterestRate property we created earlier.
Once we do that, we can add a little text before it, and we have a property control which allows users to update the rInterestRate property:
If we, for example, update the slider to 0.08, the second Bar Chart will update accordingly:
Later on, we can add another property control, this time an input box, to allow the user to adjust the number of years as well:
If you are interested getting formal training on all new 3.1 features, please register for one of our 3.1 delta trainings. They are delivered using our blended training model which provides access to asynchronous lectures and demos for 30 days, and then provides access to a live 2 hour webinar to review concepts and ask questions of a live instructor. Exercises and solutions are available as well for people to download and use when they have 3.1 installed, or they can just take the training without doing the exercises (no need to have 3.1 installed to get value from the course). The course is $300 globally.
Topics include new ways to create parameterized applications, enhancements to coloring, including conditional coloring, calling S+ and R scripts and functions, new and updated visualizations and statistical capabilities, API updates, Administration updates.
Click here for registration details.
|
-
Data preprocessing describes the concept of processing raw data to prepare it for analysis. The preprocessing will transform the data into a format that will be more easily and effectively processed for the purpose of the data analysis. There are a number of different tools and methods used for preprocessing. Some people will perform preprocessing in a database, others will perform it in a 3rd party application, and yet another solution is to use Spotfire to both pre process your raw data and also analyze the output.
Even though Spotfire is quite good at supporting large scale enterprise-wide analytical applications, it is also useful for smaller-scale analysis. Typically, in this scenario, the data that needs to be quickly analyzed is not in a suitable form, but there is too much overhead pre-processing them in another application. This is where some of the built-in capabilities come in handy. Spotfire provides a variety of capabilities to pre-process data before (and after) it is loaded inside Spotfire. One such capability we will look at in this article is unpivoting and pivoting.
Assume you have survey data which asks a series of questions where responders can choose one of many possible responses. When the data is collected, it may be stored in a data table as shown below:
However, we want to analyze the distribution of results for each individual question. To do this, we need to transform the data. But how? This is actually a two step process.
The first process is we need to normalize the data so that we have a single column for the questions asked in the survey and then another single column for the responses. We do this by using the ‘unpivot’ transformation. We select the questions we want to analyze as the ‘Columns to transform’ as shown in the screen shot below:
At this point we have the normalized data we need. The next step is we need to then pivot the data to have a row for each unique question which has fields showing the count for each response for the question.
You can do this using the ‘pivot’ transformation to pivot as shown below.
Using TIBCO Spotfire Professional, you can combine both the unpivot and pivot into the data access process so this process happens before the data is even loaded into TIBCO Spotfire (or you can also do this to the raw data set after it is loaded in TIBCO Spotfire so you can have both the raw data and the transformed data into Spotfire at once):
The final result is one row for each question and a column for each response:
If you have many different questions with different responses, then you should unpivot and pivot each grouping so they end up as a different data table in Spotfire.
|
-
A couple weeks ago we did a post that discussed how to rank something over various groups in their dataset. As a variation of this, many situations arise where customs need to find values in a specific column inside an expression available in another column. We will explain this scenario with an example.
Let’s assume we have the following data set: 
As part of the analytics required for a workflow, we may need to retrieve the value from the Volume column that corresponds to the last Date entry per month. This value will eventually be used in another expression. So , for example, using the data above, for November 2009, we want to use the Volume value from Nov 20,2009 (the last entry in November). This value is 32212.
First, we need to use what we learned in our post from a couple weeks ago to create a unique id column for each month. For this we can create a calculated column using the expression below , and call the column UID:
integer(Year([Date])*100 + Month([date]))
Now, what we need to do is ideally use the expression below to get the value from the Volume column:
Sum(if(([Date])=(Max([Date]) OVER [UID]),[Volume],0))
However, this will not work, as we cannot put the Max([Date]) aggregate function inside another expression like this.
To solve this, we need to add an intermediate step, which is to create another calculated column. This calculated column will contain the last date entry per each month:
Max([Date]) OVER UID
Once we have this, assuming the newly created column is called “Max Date per Month”, we can use the following expression to retrieve the value from the Volume column for the last entry in each Month:
if(([Date]) = ([Max Date per Month]),[Volume],0)
To go one step further, what if we wanted each row to have the value corresponding to the last entry for that month, so all rows from November 2009 would have the value 32212? For this, we would need to add another OVER statement to group by the Max Date per Month.
Sum(if(([Date])=([Max Date per Month]),[Volume],0)) OVER ([Max Date per Month])
Since this has an OVER method in it, we need to add an aggregation method. For this, we used Sum. 
|
-
This week’s tip explains two concepts for drilling down into your data: marking and filtering.
Marking is the ability to visually highlight records of interest very easily. The effects of marking are not just to visually highlight data on the page, it is also so that we can apply further analytics to that marked data, whether it is applying statistics to it, exporting it, displaying it in another visualization, or a variety of other capabilities. In this tip we will look at using marking to create details visualizations.
In the example below, we show sales data from stores in four locations: Boston, Los Angeles, New York, and Seattle. Suppose we want to drill-down into the data and only look at sales from the Los Angeles stores?
For this, we can setup a details visualization, which will only show data that is marked in another parent visualization.
Assume the Bar Chart below is the parent visualization:
If we want the details visualization to be a Line Chart, only showing data marked in the Bar Chart, we can right click in the Bar Chart and select ‘Create Details Visualization > Line Chart’.
This will create a new Line Chart that appears just like any other Visualization. We can configure it just like any visualization also. However, there will be no data loaded in it by default.
Since this is a details visualization, we only see data when records are marked in the Bar Chart.
With Spotfire, you can also define multiple markings, so you can create a series of unique markings, each their own details visualization(s), which can also span across multiple pages. We will see an example of this later on.
We can also combine marking with filtering. Filtering allows us to filter out records based off certain criteria using the Filter Panel.
Suppose we only want to look at one specific age group in our analysis? Let’s say, for example, we now want to only show customers in Los Angeles who are in our target age group of 45-60. We can then update the ‘Customer age’ filter in the Filter panel to only show records between 45 and 60 using the slider.
This will now update all our Visualizations to only show records where the customer age is between 45-60. Combine this with the criteria we already used to drill-down into Los Angeles stores only, we are able to see and analyze a specific cross-section of our customers.
Want to see what happens when you put this all together? View our latest Analytics in Action file where we walk using through a Spotfire analysis to help the store answer two analytical questions: Which 50 customers should they target for a marketing campaign, and which single customer should they select as a new spokesperson. You will see marking and filtering, combined with some other Spotfire techniques like bookmarks, used to create a Spotfire analytical file. Also note that use of multiple markings, each with their own sets of details visualizations…try doing this in Excel.
http://ondemand.spotfire.com/Public/ViewAnalysis.aspx?file=Public/Spotfire%20Analytics&waid=b4ffe34134c8533b76e30-c14e
|
|
|
|