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

Tip of the Week

  • Calculations using Fiscal Years with TIBCO Spotfire 5.5

    Many users of Spotfire use it to analyze sales data and trends over time. However, many companies do not use the calendar year for their fiscal year. Up until now, we had to use a couple calculated columns to adjust for a custom fiscal period.

    Starting in version 5.5, there is built-in support for custom Fiscal Years.  There are a couple options to implement this.

    First, there is a new built-in Document Property called ‘FiscalYearOffset’, which can also be set as Preference on the Server. This allows you to set the number of months from the start of the calendar year to the start of the fiscal year. For example, -1 will mean the Fiscal Year starts in December.
     

    Fiscal Year

     Fiscal Year 2

    Then, there are some new expression functions available for Fiscal Calculations.


     Fiscal Year 3

    Assume we have a standard cumulative sum expression, for example, on the value-axis. We can update the category-axis, to us the FiscalBinByDateTime function. This function acts very similar to the  pre-existing BinByDateTime function, except it takes in an optional fourth argument, which is the offset in months. If no offset is used, it defaults to the setting of the FiscalYearOffset Document Property.

    <FiscalBinByDateTime([Order Date],"Year.Quarter.Month",1)>

    Additional functions are available for FiscalMonth, FiscalQuarter, and FiscalYear calculations using the FiscalYearOffset Document Property.
     

  • Hiding and Showing Data in a Visualization

    Many times when dealing with large datasets, users have a need to filter to relevant data within a specific visualization. However, they want to do it in a way that the full data set is still used to ensure correct expressions and calculations are applied.  For example, in a Bar Chart, users may only want to see the top 5 and bottom 5, but not all the ones in the middle. This can be accomplished in 5.5 using the ’Show-Hide Items’ option within a visualization.

    If you look at the Bar Chart below there are lots of categorical values on the Category Axis and it makes it hard to interpret.

    Hide Show 

     
    What if you did not care about how a specific category compared to all others, but you just wanted to see how a specific category compared to the top and bottom 5 in that grouping?


    With Spotfire 5.5, you have the option to add rules to hide and show data. This is available from the ’Show/Hide Items’ option on the Visualization’s Properties dialog.

    Hide Show 2 

     
    By clicking on the ‘Add’ button from within the Show/Hide Items option, you can add in rules similar to the Conditional Coloring rules.  When defining a rule, you can specify whether the rule will be used to hide items or show items. For this specific example, we can add a rule that shows we will show the top 5 values for the ‘Sum(Sales)’ expression.

    We can then add another rule which says to show the bottom 5 values for Sum(Sales). 

    Next we can add an ‘Equal to’ rule and then set the value either as a hard coded value or as an expression and set it to the name of the specific value in the category column. In this case we are analyzing sales across various product categories from the ‘Product Category 3’ column so we use the following expression: First([Product Category 3]). We can hard code one specific product category, ie ‘METAL BOOKCASES’

    Hide Show 3 

    NOTE: If adding the value using the ‘Value’ option, you don’t need to add quotes around your string. If you add it via the Expression option, you do need to add quotes.

     Hide Show 4

    Our Bar Chart should now show the top 5 and bottom 5 values as well as a bar for our specific category METAL BOOKCASES.

    Hide Show 5 


    Then we can go into the Color axis and add an expression that will color the specific value we just added (METAL BOOKCASES) differently than the others so it is called out (NOTE we could have also done this by adding in a coloring rule)

    Hide Show 6 

    To take this example even further, using Property Controls, we can add a Drop Down Control that allows the user to select a value from the Property Category Column.  We can then use this to dynamically update the show/hide rule and color expression. This will allow the user to select a specific value, and display and color that value compared to the top 5 and bottom 5 in the Bar Chart.

     Hide Show 7

    In addition, another nice enhancement in 5.5 is the ability to use Properties inside a visualization title. Now we can update the Drop Down Control to select a product category and have the title dynamically reflect this.
     

    Hide Show 8

     

    Hide Show 9 

  • Data Comparisons using Subsets

    Data comparisons are commonly visualized in Spotfire. These comparisons can be done using an imported categorical column (like comparing regions from sales data), using tagged data (like comparing outliers), or using Calculated Columns or expressions that use case or if statements. However, there are many situations when you cannot perform a comparison on various subsets in the same visualization. For example, up till now, comparisons only work across Data Columns and values, what if you wanted to include filtering or marking collections in your comparison? You may want to compare:

    • Filtered to unfiltered data
    • Filtered to all data
    • Marked data to all data

    TIBCO Spotfire 5.5 introduces the concept of ‘Subsets’, to show multiple subsets in a visualization. Let’s start by looking at the default options.  In the properties dialog of a Bar Chart ,you will  see a new section called ‘Subsets’. If you open that section, there are 3 default subsets which you can select and the ‘Current filtering’ options is checked by default. This means that all visualizations start off behaving just like they used to by working with the active filtering scheme specified in the visualization’s properties.

    Subset1  


    What happens when we want to compare various Subsets? Let’s take a look at a few example use cases.

    Suppose we want to look at a Parallel Coordinate Plot to show our customer’s buying patterns across the 6 departments we offer (Electronics, Furniture, Garden, Groceries, Clothing, and Toys).
     

    subset2 

    Now suppose we want to filter to some specific insight we are looking at. If we do that currently, we can only visualize the un-filtered data. In 5.5, we can setup a ‘Subset’ in the visualization properties panel. We can select both the ‘Current filtering’ and ‘Not in current filtering’ options.
     

    subset3 


    We can then use the keyword ‘(Subsets)’ in our axis expression. In this example, let’s choose the Color axis, and color the ‘Not in current filtering’ to be light gray.
     

    subset4

    We can now perform filtering and see how our filtered data compared with our filtered- out data.
     

    subset5

    Let’s take a look at another example.

    Assume we are using Spotfire to analyze data across two groups, a and b.

    We can create a Line Chart which shows the average quantity for each group each day. This is achieved simply by setting the Color axis to the Group Column.
     

    subset6
    Now what if we want to analyze our individuals in the Group one at a time and compare them to the average of the groups? To do this, we can choose to Subset by ‘All data’ and ‘Current filtering’.

    subset7 


    We can then Color by both Subsets and the Group column. After Filtering to a specific individual we can see their quantities in comparison to the two groups.

    subset8  

    subset8a 

    Subsets also can be created using Custom Expressions. This allows us to perform Dimension free Data Exploration by allowing us to compare various groupings across Columns. For example, let’s assume we are looking at some transactional sales data.

    subset9

    Similar to the previous example, we may want to look at sales over time for various categories. For example how do the sales for ‘Department/Specialty’ category compare to a single Merchant, like REI. Rather than using filtering, we can create and expression:

    subset10   
    This allows us to not only compare values from a column against each other (which you could do with an if statement previously on something like the color axis), but also compare values across multiple column categories.

     

    subset11

     

     

    If you are interested in learning more about Subsets or other features of TIBCO Spotfire 5.5, please watch our new FREE 5.0 to 5.5 Delta Training Jumpstart at http://spottrain.tibco.com/sln/course/view.php?id=134 . When prompted you can login using the 'Proceed without an account' button. Also continue to watch our Spotfire Demo page at http://spotfire.tibco.com/demos as we begin to roll out 5.5 feature demos.

  • Predictions using Multiple Linear Regression in TIBCO Spotfire

    Multiple linear regression analysis is one of the most commonly used statistical modeling techniques in the business world for predictions.  In this tip we will look at how to do multiple linear regression analysis inside TIBCO Spotfire (from version 5.0 on). In this use case, assume a hotel chain is looking to understand where to build its next hotel.  They have identified 20 possible site locations and their goal is to select the one which will give them the highest possible profit margin. They have a hypothesis that the following metrics are explanatory (or predictor) variables for making predictions to hotel’s profit margin:

    • The number of competing hotel rooms within a 5 mile area
    • Distance to the nearest hotel
    • Size of local office space in a 10 mile area
    • Number of students enrolled in colleges within a 10 mile area
    • Median household income of town locals
    • Distance to town center

    To start with, we take a sample of already established hotels in the chain and load into Spotfire.  The data includes each hotel’s profit margin as well as values for the variables listed above.

    Now we can apply one of the regression models from the Tools menu in Spotfire Professional (starting in version 5.0).

     


    In this case, we will be doing regression modeling, so we choose that tool.  In the resulting dialog, we configure the inputs for our model. 

    Our response column, or the column we wish to predict, is the profit margin column, and the predictor columns are the variables we listed earlier. We leave the regression type as linear.

    After we fill out the fields in the tool’s dialog, we click OK and a model will be generated for us to use.

    When the model is generated we also get a few new Data Tables, model summary information and Visualizations to help us evaluate the model.  We can create additional models using different combinations of the predictor columns till we find the best model.

    Once you have the model you want to use, you can now load a separate Data Table that includes metrics from the various lots which you may purchase for your new hotel.

    You can then predict the profit margin for these lots, using the model you previously created, by going to the Insert menu and selecting the ‘Predicted Columns…’ menu


    In that menu, you will select the model you wish to use for the prediction, which Data Table contains the data you want to predict, and then you need to match the Columns from the model’s Data Table to the columns from the predicted Data Table (in our example the column names for the various metrics , like Office, are identical in both Data Tables).
     

    When you complete this, a new column called ‘Predicted’ will be added to all your potential site locations. From here you can see which site is predicted to have the highest profit margin.

    You can then analyze additional details about the site location’s to determine the best location.  This is done by mashing up with another data source specific to each site location which includes variables for potential taxes, price of the lot to purchase, expected building costs, etc…

    For more information on other types of regression and classification modeling please take our free v4.5 to v5.0 Delta Training Jumpstart at http://spottrain.tibco.com/sln/course/view.php?id=114. Click the ‘Proceed without an Account’ button to login as a guest if you do not have an account.

     

  • Performing in-database connections and calculations with TIBCO Spotfire

    Included in the latest release of TIBCO Spotfire (5.0) is the ability to connect to external data sources so that all calculations occur in-database rather than in-memory. This allows users to analyze larger data volumes as these calculations can be done before the data is passed back into Spotfire.

    Let’s examine a use case for this functionality.  Assume you are trying to analyze sales data for your store chain. You may want to start your analysis by showing the average sales across each region as shown below.

     

    If this dataset has 1.6 million rows, is this really needed to start your analysis? How many data points are really used in visualization above? 2 columns (Avg (Total) and Business Location) and 4 rows (Boston, Los Angeles, New York, and Seattle).

    Why should you bring all 1.6 million rows back into Spotfire only to aggregate it  in your starting visualization which only requires 4 rows?

    What you can do in this case is have the database perform the aggregations and only return the 4 rows.  Before version 5.0 you could do this by setting up an Information Link which includes a column that specifies the avg([Total]) and the Group By is set to the Business Location Column.  In additional, the distinct conditioning is applied to the Information Link.

    This works well, but when you want to update the aggregation or use more complex group by expressions, you need to update the Information Link (which not every user has access to do).

    In 5.0, you can now perform the same concept directly in TIBCO Spotfire Professional.

    To start, let’s create a connection to the same SQL Server database we used above. This time we will connect to it as an external datasource so we can do all the calculations in the database.

    We do this by going to the File > Add Data Tables menu and then selecting the Connection To option  from the Add  button. You then select which of the supported databases you want to connect to.

     



    You will then be presented with a connection dialog. You will enter the connection details to the SQL Server database and select the database you want to connect to.
     



    Finally we pick which database tables we want to connect to.
     



    When we are done, we have added a new External Datasource in Spotfire Professional.

    We can then create the same exact Bar Chart visualization as we created earlier, but only the data that needs to be visualized is returned. Since there is an aggregation on the Y-Axis (Avg),  this means Spotfire generated and optimized a SQL statement based off the visualization’s configuration and then sent the statement over to the datasource to execute. Then , only the resulting data is returned and visualized on the Bar Chart.

    If the user adds a Filter and filters some data, Spotfire will generate and optimize a new SQL statement to the datasource, which will re-excecute, perform the calculations on the datasource, and return the  resulting data to be visualized.  



    The advantage of using this approach over the Information Link mentioned earlier is that the author can change the aggregations and calculations used without having to update anything in an information model.   

    Let’s assume we don’t want to use a simple Bar Chart, but we want to use a Cross Table which shows  average sales across the various departments by age group and gender.

    We can create this visualization in Spotfire, and using the External Datasource connection, all calculations are performed in the database directly and just the data needed to visualize the Cross Table is returned.

     

  • Passing Marked Records to External Web Pages - Part II

    Last week we discussed how to create a Mashup to send information from the Spotfire Web Player to an external web site.  As a follow up to this, you may have a requirement to do this from Spotfire Professional only (not from the Spotfire Web Player). In this scenario, this easiest approach is to reuse the Collaboration Panel along with a Script Control. The Script will collect the necessary information from the marked records and then will pass it to a website that is displayed inside the Collaboration Panel.


     
    The first thing the Script needs to do is return the currently marked records:

    rowIndexSet=Document.ActiveMarkingSelectionReference.GetSelection(Document.Data.Tables["Car Data"]).AsIndexSet()

    Next, the Script will check if the marked records Index Set that was returned is empty or not. If it is not empty it will retrieve the value from the Make and Model columns for the Marked Records. (In this Script we just retrieve the first row from the marking, but this can be updated to retrieve all rows if multiple ones are marked)

    if rowIndexSet.IsEmpty !='false':
        make = Document.Data.Tables["Car Data"].Columns["Make"].RowValues.GetFormattedValue(rowIndexSet.First)
        model = Document.Data.Tables["Car Data"].Columns["Model"].RowValues.GetFormattedValue(rowIndexSet.First)

    Then we build the URL to pass into the Collaboration Panel. That API requires a Uri object to be passed in for the URL.

    urlString = "http://www.cars.com/go/search/newBuyIndex.jsp?stkTyp=N&tracktype=newcc&mkId=20006&AmbMkId=20006&AmbMkNm=" + make + "&make=" + make + "&AmbMdNm=” + model +  “&model=” + model
    link = Uri(urlString)

    Finally we get a hold of the Collaboration Panel from the Panels collection. We make sure it is visible and then set the Url property to the Uri we just created.

    for panel in Document.ActivePageReference.Panels:
      if panel.TypeId == PanelTypeIdentifiers.CollaborationPanel:
          if panel.Visible == False:
             panel.Visible = True
          panel.Url = link


    The complete Script is shown below:

    from Spotfire.Dxp.Application import PanelTypeIdentifiers
    from System import Uri

    rowIndexSet=Document.ActiveMarkingSelectionReference.GetSelection(Document.Data.Tables["Car Data"]).AsIndexSet()

    if rowIndexSet.IsEmpty !='false':
        make = Document.Data.Tables["Car Data"].Columns["Make"].RowValues.GetFormattedValue(rowIndexSet.First)
        model = Document.Data.Tables["Car Data"].Columns["Model"].RowValues.GetFormattedValue(rowIndexSet.First)
        urlString = "http://www.cars.com/go/search/newBuyIndex.jsp?stkTyp=N&tracktype=newcc&mkId=20006&AmbMkId=20006&AmbMkNm=" + make + "&make=" + make + "&AmbMdNm=” + model +  “&model=” + model
        link = Uri(urlString)

    for panel in Document.ActivePageReference.Panels:
      if panel.TypeId == PanelTypeIdentifiers.CollaborationPanel:
          if panel.Visible == False:
             panel.Visible = True
          panel.Url = link

  • Passing Marked Records to External Web Pages

    In previous tips we have discussed how to use the Web Player APIs to define what data to load into TIBCO Spotfire.  In this tip, we will show how you can take interactions from a given analysis file (like marking) in the Web Player and send related information to an external web page.

    In this scenario, let’s assume you are using Spotfire to analyze data on mutual fund’s historical performance.

    You may have an analysis page which includes a Scatter Plot that compares the returns of each of the funds over the past decade and the past year to give you an idea of its consistency on returns.


    Suppose as part of your analysis session that you want to mark a fund or funds of interest and then pass the symbols for those funds to an external web site for further analysis or information.  In our case, maybe we want to link to Yahoo Finance so we can see news on the funds or links to other pertinent information.  Whatever the specific requirements may be, we need outside information other than just raw data to complete our analysis.


    Rather than forcing the consumer to manually go to a web page and type in the necessary information, the Spotfire Web Player APIs make it straightforward to automate this workflow.

    We can create a web page that has two panes. The Spotfire Analysis file displayed in the Web Player on the left pane and then Yahoo Finance in the second. We can also create navigation controls that will allow the user to maximize and resize either pane.

    Then, using the Web Player APIs, we can listen for when records in the analysis file are marked. When they are, we get the value associated with the Symbol column and pass it as a query parameter to the Yahoo finance site and then refresh the page. The yahoo finance site takes in a query parameter with the name s which stands for symbol, so if we are passing in the symbol EQPGX , the URL would be http://finance.yahoo.com/q?s=EQPGX.



    You can see the final solution at http://bit.ly/stockMashup


    If you are interested in learning more about how to use the Web Player APIs, please consider taking our SP211 Web Player API online training course.  


  • Adding Bullet Graphs to Graphical Tables and Text Areas in TIBCO Spotfire

    Do you use dynamic items, a concept introduced in TIBCO Spotfire 4.0 and discussed in a previous tip of the week? These items, that is, sparklines, icons and calculated values, are available in Graphical Tables and Text Areas intended to provide dynamic information in a condensed format. In TIBCO Spotfire 4.5, a fourth dynamic item is obtainable; the bullet graph.


    A bullet graph offers a visual comparison of two values on an axis. One value is represented by a horizontal bar along the axis, and the other value by a line placed vertically to it.

    Let us give a hint of how bullet graphs can be useful to, at a quick glance, get an overview of the current situation. Do remember that the bullet graphs are dynamic, that is, respond to changes like filtering!

    Below you have sparkline columns in a graphical table presenting on a monthly basis Sales and Cost respectively for a number of different fruits and vegetables.

     

    Assume you are interested in comparing total Sales and Cost figures over the period, but only on a very sketchy level.  Instead of displaying and comparing the actual Sales and Cost amounts, which you can do by means of adding a calculated values column, add a bullet graph column! Let the bar in the bullet graph illustrate Sales and the vertical line Cost.  (How to add dynamic items to a graphical table is described in the tip of the week titled Information at a Glance with Graphics Tables.)


    Open the Bullet Graphs section in the Bullet Graph Settings dialog (in the Graphical Tables Properties dialog, the Axes section, add a Bullet Graph, then select the Bullet Graphs section in the opened Settings dialog). Specify what to display as the bar in the bullet graph via the Calculate values using selector, what to indicate by the line via the Calculate comparative values using selector, and which colors to use:
     

    As you can see in the graphical table below, the resulting bullet graphs tell us Sales exceed Cost for all types of fruit and vegetables.  It seems selling all types is moneymaking, and selling bananas generates highest sales amount.
     

    Now let us elaborate this a little bit further. Say, if you consider other expenditures, you have to add 20% to the cost prices to cover up for extra expenses. Is selling all types of fruit and vegetables still profitable? To answer the question, add another bullet graph to the graphical table where the vertical line shows Cost multiplied by 1.2. (To enter the expression, right-click the selector to open the Custom Expressions dialog.)


     
    If you look at the added bullet graph column below, it is obvious at a quick glance you would lose money if selling only bananas, as expenditures and purchase prices are not covered. Instead it seems you should go in for selling more pears!


     
    Finally, bullet graphs can be added also to text areas in the same way as other dynamic items. Note that in text areas, a scale is added.


     
    To learn more about bullet graphs and other dynamic items, please consider taking any of the Mentored Online Training courses.

  • Performing a YTD Comparison on a Partial Year

    One common question we see a lot is how to compare and analyze data from one year to the next. There are a variety of ways to do this. Let’s assume we have data like shown below, which includes transactional data of sales by sales rep and by region for 2011 and 2012.


    If we want to analyze the results per region, for example, we could create a Bar Chart which shows Region on the Category-axis,  and  Sum ([Total]) on the Value-axis. We can additionally color by Year([Order Date]).

    You will notice that the sales are all less in 2012, in some places significantly less. If we take a look at the raw data, we can see it is because the results for 2011 are complete, but at this given point in time, we are still in 2012, so we don’t have data through the end of the year.


    What if we want to compare how we are doing so far this year compared to the same time period last year?  Using a simple calculation, we can build a calculated column, called ‘Adjusted YTD’ which will show data only if the day of the year for 2011 is before today’s date in 2012.

    if(DayOfYear([OrderDate])<=DayOfYear(DateTimeNow()),[Total],0)

    If today is October 14, 2012, it will output sales totals for all rows in 2011 where the date is October 14,2011 or earlier, but for October 15th,2011, for example, it will output 0.


     
    We can then update our Bar Chart to use the Adjusted YTD Calculated Column  on the Value-axis, so that we can properly show an Actual YTD comparison.


    If we now want to show the just a single bar which shows the difference between the Adjusted YTD this year versus last year, we can use the following custom expression on the Value-axis:

    Sum([Adjusted YTD]) - Sum([Adjusted YTD]) OVER (Previous([Axis.Color]))


    Interested in learning more about calculations and custom expressions in order to generate your own analysis? Please consider taking our SP141 Computational Analytics training course. This is offered onsite with a live instructor, and online, using our new Mentored Online Training format.

  • Creating a Dynamic Top Ten Chart - Part II

    In a previous tip, we discussed how to leverage Property Controls to create a Tip 10 list based off various categories.  This tip assumed the data was setup in such a way that each category was its own column.

    (For example, every row had a customer id, as well as a how much that customer shopped in each of the 6 departments (clothing, furniture, toys, groceries, electronics, and garden) and we wanted to find the top 10 customers for any given department selected by the user in a drop down)

    What if the data is setup differently so that each category is in a single column? We can accomplish the same output, but will need to follow a few different steps.

    Assume our data is structured like the Data Table below. . .

     
    . . . and we want to create a Bar Chart that allows us to display the top 3 groups from the ‘Group’ Column based off a specific value for ‘Cat A’.  For example, which 3 groups with the value for ‘Cat A’ of True had the most ‘Value’.

    To accomplish this, we first need to create a Drop-down list Property Control to display unique values from the ‘Cat A’ column.


    Using what we learned from another previous tip, we then create a calculated column which will either output ‘Yes’ or ‘No’, depending on whether the value from ‘Cat A’ matches the value selected by the user in the drop down we just created.


    if(find([Cat A],"${CatAFilter}")>0,"Yes", "No" )


     
    We should then uncheck the ‘No’ value in the Filter Panel for this newly created Calculated Column. Now, the resulting data only shows values that match the drop down.  If the user selects ‘False’ in the drop down, then only rows in ‘Cat A ‘ where the value is ‘False’ should be displayed as shown below.


    As we mentioned in the previous tip , it is a good idea to hide the Filter for the newly created calculated column, so that users in the Web Player do not accidentally update or reset it.

    Once we have this, we now need to rank the remaining rows.  To accomplish this, we will create another calculated column.  This will use the Rank function, to rank in descending order the ‘Value’ Column grouped by the ‘dynamicFilter’ calculated column we just created.
     

    Rank([Value],"desc",[dynamicFilter])

    Next, just like in the previous tip, we create a Bar Chart to show the ‘Group’ values on the category axis, and on the value axis, we use the ‘Value’ column. We also configure the Bar Chart to sort the Bars by height. We then use the ‘Limit Data Using Expression’ property to display only the top 3 Groups.


    if ([Top 3]<4, True, False)
     

    To extend this, if you wanted to display the Top 3 based off values from both ‘Cat A’ and ‘Cat B’ columns, you would create another Dropdown list Property Control to display unique values in the ‘Cat B’ column and you would update the ‘dynamicFilter’ Calculated Column to the following expression:


    if(find([Cat A],"${CatAFilter}")>0 AND find([Cat B],"False")>0,"Yes", "No" )

    Interested in testing your skills to see how much you know about authoring in Spotfire? Try our newly released Spotfire Author Assessment. It is a 60-question exam covering all topics related to authoring and report development in TIBCO Spotfire Professional. The exam is hands-on and requires students to not only understand available features and functions, but also how to navigate the Spotfire Professional User Interface , and how to take data and business questions and come up with solutions using TIBCO Spotfire. The exam requires students to have TIBCO Spotfire Professional 4.x or higher installed.

     

  • Creating Multiple Columns From One

    Throughout this tip of the week series, we have displayed many solutions which utilized the combination of Property Controls and Script Controls. These two features, when used together, can create endless solutions in Spotfire.  This week we will look at one related to transforming data.

    Many times when a user loads their data in an exploratory fashion,  they realize it is not in the correct format required for analysis. Spotfire does have a series of ‘Data Transformations’ that can be applied when you load data, like Pivot, Unpivot, etc…but there are a few scenarios where these will not work.  One specific scenario is when data comes in from a log or similar and a single column includes a comma-separated list of values, like 342,234,324,546, which need to be  broken down into separate columns for each value (4 columns in this case).

    We can use the powerful Regular Expression functions included in our Calculation Column expressions to parse the column, but that will only work  when you have a known quantity of delimiters in each column and even then you would have to write the expression for each new column desired manually.


    If you wish to automatically detect the number of delimiters and then loop through to create multiple new columns at once, you can utilize the combination of a Property Control and a Script Control.

    First, we createa a Drop-down list Property Control which allows you select which column to transform.

     

    Then we create a Script Control to break up the selected Column into multiple Columns based off the comma delimiter.  The Script will first create a Column that counts the number of delimiters for each row. It will then loop through all rows in the dataset and create a new Column for each value before each delimiter.So, for the value 342,234,324,546, the result will be one new Column that counts the number of delimiters( 4 in this case) , and then 4 new Columns, for the specific values. For this specific row, the values would be 342, 234, 324, and 546.

    The Script to accomplish this is shown below. It assumes the Property which is attached to the Drop down list Property Control is called 'myColumnSelection'

    curDT = Document.ActiveDataTableReference
    cols = curDT.Columns
    targetCol = Document.Properties["myColumnSelection"]

    #Create a new column that counts the comma delimiter
    myExpression = '1+len(RXReplace(string([${myColumnSelection}]),"([A-Za-z0-9]+)","","g"))'
    myNewColName = cols.CreateUniqueName("NumElements")
    cols.AddCalculatedColumn(myNewColName, myExpression)

    #Get max number of elements
    maxElements = curDT.Columns.Item[myNewColName].RowValues.GetMaxValue().ValidValue

    if maxElements > 1:
        #Generate Columns upto but not the last item
        index = 1
        while index < maxElements:
            myExpression = 'RXReplace([C],"((\\\d+)[,]){' + str(index) + '}.*","$2","")'
            newCol = targetCol + str(index)
            myNewColName = cols.CreateUniqueName(newCol)
            cols.AddCalculatedColumn(myNewColName, myExpression)
            index = index + 1

        #Generate Column for last item
        myExpression = 'RXReplace([C], "((\\\d+),){' + str(index-1) + '}(.*)", "$3", "")'
        newCol = targetCol + str(index)
        myNewColName = cols.CreateUniqueName(newCol)
        cols.AddCalculatedColumn(myNewColName, myExpression)



    While this solution may work well for ad hoc analytics, the down side is that the data is loaded first in Spotfire, and then transformed. A more production ready version of this would leverage the Spotfire SDK to build a Custom Data Transformation. With this approach, the transformation happens before the data is loaded and displayed in Spotfire Professional. In addition, it  would automatically be re-applied as data is reloaded or replaced.


  • Writing and saving comments inside a Spotfire Analysis file

    Many times, users would like to collaborate with each other in the same Analysis file but don't  have a specific collaboration tool, like TIBBR (http://www.tibbr.com) to use. By leveraging the power of Property Controls and Script Controls, you can create a page on your Analysis file, which allows users to view and share comments ( which works in both the Web Player and Professional client).


    First we need to setup an input box to capture the user’s comments. We do this with an ‘Input Field (multiple lines)’ Property Control:



    We can also add a descriptive heading above the Input field as shown below:



    After that, we need to create a place to store the comments.  For this, we create a Document Property and attach it to  a ‘Label’ Property Control. To maximize real estate , rather than putting the Label Property Control underneath the Input Field Property Control, we leveraged the concepts discussed in an early tip, to place them in a HTML table side by side.

    In addition, we were able to style the Label by putting a border around it so its easily visible to the user:
     

    The final step is to create a Script which will take the comments entered by the user in the Input Field Property Control  and save them to the Document Property we just created (which will update the display in the Label Property Control).  

    Below is the Script.  It assumes the Document Property for the saved comments (attached to the Label) is called savedComments and it assumes the Document Proeprty for the comments the user currently entered (the input field Property Control) is called inputComment

    from System import DateTime, Threading
    strComments = Document.Properties["savedComments"]
    strUserInput = Document.Properties["inputComment"]

    # Get the current time ie 09/15/2012 08:17:31
    timestamp = DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss")

    # Get the currently logged in user’s username
    username=Threading.Thread.CurrentPrincipal.Identity.Name

    # Create the comment lines by appending the timestamp and login username following by the comment on a separateline
    commentToAppend = timestamp + " - " + username + "\n" + strUserInput
    if strComments == "":
       newLine = ""
    else:
       newLine = "\n\n"
    strAppendedComments= strComments + newLine + commentToAppend

    #Append the comment lines to the savedComments Document Property
    Document.Properties["savedComments"] = strAppendedComments

    #Clear the Input Field Property Control of the previous input
    Document.Properties["inputComment"] = ""

    When executed, this script will capture the user's login name, the timestamp, and then the comments and append to the the savedComments Property. If you wish to prepend comments (so the newest show up on top) change the following line in the script:

    strAppendedComments= strComments + newLine + commentToAppend

    to

    strAppendedComments= commentToAppend + strComments + newLine

    Since Document Properties are automatically persisted within the Spotfire Analysis file, the comments are saved and stored.



    Interested in learning how to build your own solutions like this? Please consider taking our SP232 Automation APis with Iron Python course using our Mentored Online Training delivery model.

  • Creating a Dynamic Top Ten Chart

    We’re back!

    This tip will be the first one of our fall series.  Thank you to everyone who reached out via email to check on the status of the Tip of the Week Blog.   It was nice to see how many followers we have who wanted the tips to return.

    This week we look at how to use Property Controls and Calculated Columns to create a plot which dynamically updates to show the top ten values of a category, where the category can be selected via a Drop-down Property Control.



    Assume we are working with data which shows sales across various departments and the departments are what we want to use as values in the Drop-down.  We should create a Drop-down list Property Control which sets values through 'Column selection'.


     

    Notice in the image, in the ‘selectable columns’ field, we chose to manually set which columns to include by writing the following expression:

    Name:Electronics OR Name:garden OR Name:Groceries OR Name:Toys OR Name:Furniture OR Name:Clothing

    This will ensure that only the departments are shows as options in the Drop-down list. Once we have this created, we now need to create a Calculated Column.  This column should rank the department selected from the Drop-down list.


    Assuming the Property attached to the Property Control we just created is called whichDepartments, the expression would look like the following:


    Rank([${whichDepartments}],"desc") As [Dynamic Rank]


     

    Name the new Calculated Column ‘Dynamic Rank’.  Then create a Bar Chart. The Category Axis, for our data set, should be set to  'Customer ID', since we want to show the top ten customers.  The Value Axis should be dynamically updated to be the Sum of whatever department is selected in the Drop-down.

    To do this, right click on the Value Axis and select ‘Custom Expression’. In the resulting dialog, enter the following expression:

    Sum(${whichDepartments})



    We then need to make sure the Bar Chart only shows the top ten values. To do this, starting in TIBCO Spotfire 4.0, there is the ability to limit data directly inside an visualization.  From the properties dialog, select the ‘Data’ menu and on the bottom, click the ‘Edit’ button next to the ‘Limit data using expression:’ item.


     

    The expression you should add is :

    if([Dynamic Rank]<11,True,False)
     


    What this expression will do is only show data where the ‘Dynamic Rank’ column is less than 11 (so the top 10).  The ‘Dynamic Rank’ column  will update dynamically to re-rank based off what is selected in the Property Control Drop-down. The end result is an analysis file which allows the consumer to select a department and then have the Bar Chart dynamically update to show the top 10 customers.  This is much more efficient than creating a Bar Chart for each department.


    To learn more about how to use any of the functionality explained in this tip, please consider taking any of our Mentored Online Training courses.

  • Setting Document Properties and Executing Scripts when an Analysis opens

    By using the ‘History Arrows’ navigation option, which is available starting in 4.0, it is possible to setup a page so that Scripts and Document Properties are loaded before the user starts the analysis process. 

    First, when you setup the analysis file, change the page navigation to ‘History Arrows’.

    Then, make sure the first page in the analysis includes some information about the analysis file along with an Action Control.   This Action Control can be attached to a Script which can do any of the following tasks:

    • Preset some Document Properties before the users goes into the rest of the analysis
    • Execute additional Scripts that should be executed before the user goes into the rest of the analysis
    • Set the page navigation mode back to ‘tabs’ or ‘step-by-step’ if desired
    • Redirect the user to the next page in the analysis

    Let’s assume we have a data set that looks at expense reports. These reports are filed using USD, GBP, EUR , and CAD currency. However, to analyze properly, we would like to convert everything to USD.  It may make sense that you want to convert the rates using the rates for the day the expenses were incurred, but let’s assume you want to load in the current rates at the time you are loading the analysis.   In a previous tip, we explained how to set the exchange rates up as Document properties.  


    Those properties are still using static values. What we would like to do is trigger the update of those properties using the current exchange rates.  To do this, we can use the following Script, which pulls the rates down from openexchangerates.org, and then updates the Document Properties with those values.

    from System.Net import WebClient
    from System.Collections.Generic import Dictionary
    from System.IO import StreamReader
    from System.Web.Script.Serialization import JavaScriptSerializer

    # get the data from openexchangerates
    url = 'http://openexchangerates.org/latest.json'
    wc = WebClient()
    wc.Headers.Add('Content-Type', 'application/x-www-form-urlencoded')
    requestData= wc.OpenRead(url)
    reader = StreamReader (requestData);
    response = reader.ReadToEnd()

    #Parse the results to get the exchange rates
    js = JavaScriptSerializer()
    exchangeRateDict = js.Deserialize(response,object)
    gbpRate = float(exchangeRateDict["rates"]["GBP"])
    eurRate = float(exchangeRateDict["rates"]["EUR"])
    cadRate = float(exchangeRateDict["rates"]["CAD"])

    #Set the Document Properties in the Spotfire file with the current rates
    Document.Properties["PoundConversion"] = gbpRate
    Document.Properties["EuroConversion"] = eurRate
    Document.Properties["CADconversion"] = cadRate

    In addition, using a previous tip, we can execute additional scripts from within this script  to perform any additional actions we want.

    Finally, if you want to set the page navigation back away from ‘history arrows’, you can by using the following code in your script:

    Document.Pages.NavigationMode = 0

    With 0 setting the navigation to ‘tabs’ and 1 setting the navigation to ‘links’. You can also redirect the user to the next page in the analysis:

    Document.ActivePageReference = Document.Pages[1]

    Now when the file is opened in the Web Player, the user can only view the first page in the analysis and must click on the ‘Click here to start’ button.

     

     

    Clicking on this Action Control  will trigger the Script discussed above, which will then update the exchange rates, change the navigation to tabbed, and then redirect the user to page 2 to start the analysis.


     

    If you are intersted in learning more about Script Controls, please consider taking our SP232 Script Controls course. The course was just recently updated, and now includes over 50 separate scripts used for demos or exercises. If you are intersted in registering for this online course, or learning more, please click here.

  • Adding a Filter Control into a Text Area

    Many times authors will want to allow a user to filter out values in a given column, but they do not want to expose the Filter Panel, as it can add confusion and complexity for a consumer. The workaround has been to use a Property Control to provide this capability from within a Text Area. This solution had a series of unwanted side effects if it was not setup properly.

    In 4.0, we now have the ability to embed a Filter for a specific Column directly into a Text Area.  This also allows authors to make efficient use of space and hide the Filter Panel. In addition, all the controls for a consumer are located in the same place, making it easier for them to control the dashboard.

    To add a Filter to the Text Area, select the new ‘Filter Control’ icon from the edit Ribbon inside a Text Area.

     

    You are then presented with a dialog box which allows you to specify which filtering scheme to use and then which filter you wish to add to the Text Area.

     

     

    Once you do that, the Filter Control will show up in the Text Area.

     

    While in Edit mode for the Text Area you can move the Filter around.  You cannot alter the Filter Text (ie. List Box, Radio Button, Checkbox, etc…) however.

     

    The Filter is a copy of the Filter instance from the Panel, so if you want to update the Filter Type, you need to do it from within the Filter Panel inside Spotfire Professional. That will then automatically update the Filter Type from within the Text Area.

    The use of the Filter should be identical to the Filter in the Filter Panel, with a few minor exceptions. When dragging a range slider in the Filter Panel, the visualizations are updated immediately while the dragging is still going on. The Text Area version will only update the visualizations when the dragging is finished.

    Interested in learning more about the new features of TIBCO Spotfire for version 4.0? If so, attend our live training webinar on February 29th. Click here for details.


More Posts Next page »

Syndication

Other Spotfire Blogs

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