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

Tip of the Week

September 2012 - Posts

  • 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.

Syndication

Other Spotfire Blogs

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