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.
May 2010 - Posts - Tip of the Week

Tip of the Week

May 2010 - Posts

  • Cascading Property Control Drop downs with On-Demand Data Tables

    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.

  • Dynamically Creating Plots with Property Controls

    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.

  • Creating a HTML Editor for your Text Areas

    One of the updates from version 3.0 to 3.1 is that Text Areas now use HTML rather than RTF (Rich Text Format).  However, there is no way to enter raw HTML directly in the Text Area.  By using a combination of a Property Control (to enter the HTML) and a Script Control (to display the HTML using the API), we can create an HTML editor which will allow you to enter HTML code and then create a new Text Area to display it in.  

    First, we need to create the Property Control. For this we will use a multi-select list box and size it to be wider and higher than the default.

    This will be the control that the users can enter HTML into.  

    Second, we need to create a Script Control which will create a new Text Area and then display the HTML in the Text Area.

    from Spotfire.Dxp.Application.Visuals import HtmlTextArea
    textArea = Document.ActivePageReference.Visuals.AddNew[HtmlTextArea]()
    textArea.HtmlContent = strHTMLContent

    The value from the property created earlier is passed in as an input parameter (called strHTMLContent) to the Script Control.

    When you are done, you can test it out (as shown below).  

    Although this feature will also work in the Web Player, the real use case it to allow an author to have more control over the layout and style of the text area, including tables, style sheets, remote images, and even some nifty things like audio and video.  

  • Conditional Coloring by Comparing Values in Multiple Columns

    In previous posts, we have discussed the use of conditional coloring to color based off rules such as greater than, less than, top, and bottom. In this post, we will discuss how to apply conditional coloring when comparing values in multiple columns.

    Let’s assume we have data about particular projects (although it could be data on anything). In this data table, we have columns for the "Planned Start Date" and the "Actual Start Date".


    Suppose we want to conditionally color all values in the "Actual Start Date" column where the "Actual Start Date" is less past the "Planned Start Date"?  For this, we should first add a Color scheme grouping in our table visualization which includes the column we want to conditionally color on. In this case, it’s the "Actual Start Date" column.

    We should then add a Coloring rule, with the Rule type being set to ‘Boolean expression’


    For the value of the rule, we will enter a custom expression. In this expression we will check if the "Planned Start Date" value is less than the "Actual Start Date" value.

    This expression is evaluated for each active row in the data table.  If the expression returns true, the "Actual Start Date" cell in that row will be colored.

  • Performing Logical Comparisons of Data

    There are times when you want to create a visualization that will show the relationship of various sets of data. Some of this can be done using a details visualizations, but it is also nice to be able to do this in a single visualization. For this, we will use the Pie Chart.

    Let’s assume we are analyzing stock data.  Inside our data table, we have a column called Signal, which is an indicator from analysts whether the stock should be bought or sold and also a column called Trend, which describes the current trend of the stock, whether it is going up (designated with a + sign) or down  (designated with a – sign).  If we would like to do a comparison of both of these columns to see their relationships, we can create a Pie chart which colors based off both the Signal and Trend columns.

    When we do that, you can see the finished Pie Chart:

    This chart shows us that 54.2% of the stocks signaling as Sell are on the down trend, and only 19% are on an upward trend. For stocks signaling as Buy, it is about even split between downward and upward trends.

    What if your data is not structured properly to do this type of comparison? For example what if there was no column in your raw data that comprised what you wanted to compare? You do have a couple options.

    First you can create an expression on one of the color axis. For example, let’s assume we are looking at the same stock data and we want to compare the Signal (which is in our dataset) and also which stocks have the highest volume.  We do have a column in the data table which is for the stocks Avg Volume, but its not categorical data, its continuous.  We can use an expression to make it categorical based off some logic.  We can say we want all stocks with average volumes over the UOF (Upper Outer Fence) to be labeled as Yes and then all others as No.

    To do this we right click on one of the axis in the Color legend, and select Custom Expression.

    In the expression dialog, we want to use the following expression:

      if(([Avg Vol])>=(UOF([Avg Vol])),"Yes","No") As [Highest Volumn]

    Since this color axis also has another column in it, the Signal column, we will need to use the NEST keyword.  For more information on the NEST keyword, see the link on it to the help file or consider taking our Computational Analytics training course to master the entire expression language.

    The final expression will look like the following:

    And the resulting Pie Chart looks like the following:

    Let’s show another example from another domain.  This data set contains generic sales values for 'My Product' and Total Market sales.  Customers are identified with a numeric CUSTOMER CODE, and the customers are divided into four different Class groups based on corporate opinion on how the customer should be approached.  In addition, the Potential of each customer is rated as High or Low, and information about the location (country and global region) of each customer is provided.

    When then may want to identify ‘key customers’ as those who are higher than one standard deviation above the average series value.  For this , we can create an expression , either as a Calculated Column or inside the Color Axis:

    if(([My Product Sales])>(Avg([My Product Sales]) + StdDev([My Product Sales])),"My Key Customers","No") As [My Product Customers]

    We can then do the same thing for Total Market Sales instead of My Product Sales to get those sales which are higher than one standard deviation above the average series value for all the market sales.

    if(([Total Market Sales])>(Avg([Total Market Sales]) + StdDev([Total Market Sales])),"Total Market Key Customers","No") As [Total Market Customers]

    If we put these expressions on the color axis, we can compare the logical groupings of the two.

    Representing a logical AND, how many customers are key in both My Product Sales and Total Market Sales?  The Answer is shown in blue and is 13.

    If you want to know how many customers are key in My Product Sales but not in Total Market Sales, the answer is shown in red and is 33.

    If you want to know how many customers are key in Total Market Sales but not in My Product Sales, the answer is shown in yellow and is 34.

    If you want to know how many customers are not key in either My Product Sales or Total Market Sales, the answer is shown in green and is 220.

    In our example above, we were able to categorize the column using an expression, but let’s say the column you want to use as pie of the logical comparison cannot easily be used in an expression. Then, we can rely on the Tag Panel to create a Tag Collection for this column.   Below is a picture showing the same exact data set, but the records above one standard deviation are manually marked and tagged:



Other Spotfire Blogs

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