The Spotfire Community is moving to TIBCOmmunity and this forum location has closed. During the transition, you can still search the old forums but posting has been disabled. We encourage you to pick up the discussion at the new Spotfire community on TIBCOmmunity.
October 2009 - Posts - Tip of the Week

Tip of the Week

October 2009 - Posts

  • TIBCO Spotfire Professional Quick Reference

    One request I get all the time is for a quick reference, cheat sheet, or a shortcuts handout.  Here is one we just made for this blog.  Feel free to print it out and place it somewhere on your desk when you are using Spotfire. 

    Do you have  any other shortcuts or helpful information that you would want included on this or that you want to share with people? If yes, then please reply to this blog.



  • Freezing Calculations

    Many customers create calculated columns to aide in their analysis.  Calculated columns are static columns, but if the data in the underlying columns, which are used in the calculation, changes, it is recalculated based on the new data. However, TIBCO Spotfire Professional provides a way through which your calculation results can be frozen and these frozen calculations can be used for further analysis. We will use some examples to illustrate its use. Calculated Columns Occasionally, the columns included in a data table may not contain all necessary information for analysis. In those cases, the necessary information can be computed from existing columns by using the mathematical and logical expressions provided by the Insert Calculated Column tool.  

    For our example, we will use a simple data table with just two columns, both with numeric values, to illustrate use of frozen calculations.

    Using mathematical and logical expression we calculated a new column called ‘Result Calculation’ as shown below:

    If there is a situation where data for First and Second column changes, our calculated column will also get updated based on new data values as shown below:


    As mentioned before, it is possible that you will want to keep your  original result calculation (based on our earlier data) and you will want to calculate a new result calculation based on the new column values. You may also want to compare the two result columns. If we freeze the calculated column, the data will not change, even if the data from the source columns changes.


    Freezing calculations

    In order to freeze a calculated column, click  Edit > Column Properties and select the ‘Freeze Column’ button.  

    By freezing calculated columns, you can also perform analysis across multiple data tables. Suppose you have an analysis file with the following two data tables:


    There is a common column in both data tables (Row no), and one of the data tables has a frozen calculated column (result calculation). Based on  [Result calculation] in example data table we want to create another calculated column in Second data table  .We can use Insert >columns from external data to perform this calculation  and in this case depending on our requirements we may like to have result calculation values frozen.Replace data table If you try to replace the example data table with the second data table, only the imported columns would be available for matching. Your calculated columns from the example data table are not visible.  If the calculated column is frozen, it will become visible in the matching columns dialog since the column values are frozen.   

    Interested in learning more about calculated columns? Attend our SP131 Spotfire Essentials course onsite, regionally at one of our training centers, or using our blended training.

  • Expressions are not just for the X and Y axis


    You may already be aware that you can configure the X and Y axis to use a predefined expression, rather than the raw data values from a column. This expression can perform complex aggregations, calculations between one column and other columns in the data table, or logical calculations based off the values in particular column(s).


    What you may not be aware of is that you can use the same expression syntax in other axis, like the Color axis, or Size axis.  This becomes very valuable when you are trying to color or size, for example, on something that is not built into the raw data.

    Let’s assume you have a Bar Chart that shows a distribution of values:


    You can choose to color based off another categorical column in your data set, but that may not be what you want to display in this chart. If you have many bars, you may want to color bars to put them into different bins, “Low”, “Normal”, and “High” or similar.

    You can do this in a variety of ways. The way I see most people do this is use the Tag Panel to create a Tag Collection , and then identify the different bars (in this case Z-scores) as either “Low”, “Normal”, or “High”. (We will discuss Tags in a future Post and they are also discussed in detail in both SP151 TIBCO Spotfire Distributing Analytics and Data Cleaning, Transforming, and Pre-processing in TIBCO Spotfire).

    That will work, and the benefit is you can now use those tags in another chart if needed. The downside is any new data loaded may not be applied to your tags as you want them to be.

    However another option, which will get executed against new or replaced data,  is to use an expression directly in the color-axis.  To do this right-click on the color axis selector and select ‘Custom Expression…”


    You can then enter any expression you want.  In this particular case, we want to color bar into three segments, based off values from the x-axis column.  You can use values from any column, we just happen to use the x-axis column here to keep it simple.  The following is the expression used:

    <case  when ([Z-score])>(2.0) then "High" when ([Z-score])<(-2.0) then "Lo" else "Normal" end>



    This expression will check the x-axis value and will identify the bars with values over 2 as “High”, the bars with values under -2 as “Low” and everything else as “Normal”. The names used to display inside the color selector shown in the legend.

    If you need to you can now adjust the colors for the three segments in the properties dialog.


    The expression will now be re-calculated to update the colorings when needed (when new data is added, when filtering is performed, etc…)


    Expressions can also be used in the size axis.  Although this expression is much more complicated, the following expression is used in the size-axis to size a marker based off the number of highly discounted items.



    This expression, as well as the entire concept of Expressions, is discussed and explained in detail in the training course SP141 : TIBCO Spotfire Computational Analytics and the webinar  Custom Expressions in TIBCO Spotfire



  • Using the Intersect method in a Custom Expression

    Many people are aware of the use of OVER in a custom expression, however one issue that comes up in almost every training is when a user has created an expression using OVER on either the x or y axis the results are not what they expected. Typically this is because the plot has a defined slicing in another axis as well, like the color, trellis,  shape or size access and the OVER method has not properly taken that into account.


    Assume we have the following data table:



    If we display a Bar Chart with the Name of on the X-Axis, and the Sum of the Number on the Y-Axis, the plot would look like the one below:




    This will slice the data table by ‘Name'.   We can also add a Color-axis (as well as any other axis) as shown below:


    NOTE: Each time you make a choice in a selector dropdown, an intersection take place in your data table. (Each individual slice,  is an intersection of all  slicing properties in that particular visualization.) In the chart above, slicing is based off ‘Type' and ‘Name' and you can think of the data as being sliced as shown below:


    If we want to then create an expression that gives us the sum of the each individual item OVER the sum of all items, many try to use the following expression:


         Sum([Number]) / Sum([Number]) OVER (All([Axis.X]))


    But when we do this, the numbers are off, as you can see in the plot below.




    If you add up the totals for each Bar, it adds up to 2 not 1. Why? This is because your expression did not take into account the plot is slicing the data by not just the x-axis (‘Name' in this case), but also by Color (‘Type' in this case).  


    Since the Color axis is not taken into account in the expression, the calculation is grouping the sums by Type: ‘fruit' and ‘vegetable'.  This is why the ‘Apple' and ‘Banana' Bars add up to 1 (since they are all the fruit types in the data table), and the ‘Broccoli' Bar adds up to 1 (since it is the only fruit type in the data table). 


    To ignore the Color-axis slicing as well, we must use the Intersect method.


    Sum([Number]) / Sum([Number]) OVER (Intersect(All([Axis.X]),All([Axis.Color])))


    The result of this expression is shown below:



    The reason this works is because the OVER (Intersect(All([Axis.X]),All([Axis.Color]))) part of the expression tells us to ignore both the X-axis slicing and Color slicing. 


    NOTE: If you added another slicing axis into the plot, like size or shape or trellis, you would then also need to add that related portion into the Intersect method if you wished to ignore it when performing the custom expression calculation.



    Remember these simple steps related to slicing and the user of OVER and the Intersect method in Custom Expressions:


    • An intersection will occur each time an axis value is defined (x, y, color, trellis, shape, size, etc...) , and will slice your data inside the plot
    • Each individual unit/item displayed in the plot is an intersection of all slicings defined in that particular plot
    • The OVER method when used in Custom Expressions is really telling the plot to ignore specific slices that are already built into the plot


    Interested in learning more about Custom Expressions and how to use them? Please take SP141 Computational Analytics.


  • Choosing the Right Plot for your Data and Analysis

    TIBCO Spotfire ships with an extensive set of built in visualization types, all with their own strengths for displaying specific types of data. It is important you select the most appropriate visualization to make your data easier to interpret and more informative. 

    The goal is to provide the appropriate information to your consumers. Different visualizations can be used to deliver different messages about your data, like the distribution of the data, trends and correlations in the data, and summary statistics about your data. Different visualizations are also suitable for displaying different categories of data, like categorical or continuous, aggregated or raw, and bivariate or multivariate.

    The following figure depicts a flow that represents Spotfire visualizations that are good to use based off what message you are trying to display and what category your data is.


    Right Plot

    If you would like more information on each visualization type, when you would use it, why you would choose it over another visualization type, we have a audio based eLearning module called Introduction to Data Analysis and Visual Analytics which discussed this topic in detail in one of its modules. The datasheet for this course is available from here   and you can register for the course here.

    We also cover this topic in our Authoring Best Practices live webinar. Click below for more details and schedules:

    There will also be future posts here which will discuss specific differences between certain visualizations, like Box Plot and Bar Chart, or a Line Chart versus a Scatter Plot.

    Since it is very important to select the right visualization for your message and data, you may have business requirements to display data in a visualization that is not built-in to TIBCO Spotfire Professional. If this is the case, our SDK allows developers or Spotfire Professional Services to integrate existing charting components or built a new visualization from scratch.   The screen shot below is an example of a Gauge plot integrated into TIBCO Spotfire for a specific business requirement:



    The visualization, once integrated, can utilize all the key aspects of TIBCO Spotfire including marking, filtering, and drilldown.


Other Spotfire Blogs

©Copyright 2000-2011 TIBCO Software Inc | Privacy Policy | Terms of Use I Blog I Contact Us I Content Center