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

Tip of the Week

January 2010 - Posts

  • Setting Display Names for Expressions

    The last couple tips have been more advanced uses of expressions, so I thought I would follow those with a basic, yet powerful example.  The first thing that came to mind was to explain how to rename an expression. Even though you can create powerful and complex expressions to alter how the data in your visualizations is displayed, there are also very simple expressions. In fact, expressions are used behind the scenes even when setting an axes value through the User Interface.

    Suppose you want a Bar Chart that displays the Sum of ‘Electronics’ sold per ‘Store Location’. You can set the X-axis to use the ‘Store Location’ column and the Y-axis to use the ‘Electronics’ column and select Sum as the aggregation method. 


    When you do that, you actually have created a basic expression.   You can view the expression dialog (by right clicking on the axis and selecting ‘Custom Expressions…’) to see the expression created:


    Let’s say you do not want the axes to show Sum(Electronics). If you want to rename the output, you can use the ‘Display name:’ field , also in the expression dialog, to enter the display name,



    This works great. However, let’s say you want the Bar Chart to have multiple columns on the y-axis (or any access for that matter). For example, the Bar Chart below shows both the Sum of Electronics and the Sum of Furniture on the Y-axis.



    The expression used internally for this is:



    With this, we cannot use the display name field to rename the output as there are multiple columns used .  If you want to rename the output expression for each column, you must use the As keyword inside your expression.

    The As keyword will be paired with an expression and allow you to specify an output that is displayed anywhere that expression should be displayed (in all the axis selectors, tooltips, labels, etc…)

    In this example, we can right click on any of the y-axis columns and select ‘Custom Expressions…’ and enter the following in the expression dialog:


    You can see from the result below that anywhere the expression is used, in this case the Color By and y-Axis, the display names set from the As keyword are used.


    When using the As keyword, if your desired display name is a single string without spaces, you can just enter it as is (i.e. As Electronics). However, if your display name is multiple strings, you need to enclose the name in brackets (i.e. As [Total Amount of Electronics Sold])

    The As keyword as well as many other features are discussed in our SP 141 Computational Analytics class , which can be taken using one of our various delivery options: instructor-led onsite or public, blended training, or our bootcamp offering.


  • Calculating Number of Business or Working Days

    One request that we get a lot is a way to calculate the difference between two dates, but only using business or working days (so not including the weekend).  As with many of these types of solutions, we will need to create a new calculated column to hold these values.


    If we just wanted to calculate the number of days between two dates, we can do that easily using our DateDiff function available anywhere you can create an expression in Spotfire. For example, assume we have a column called ‘ORDER DATE’ and another one called ‘DELIVERY DATE’, the expression to calculate the number of days between them would be the following:


    DateDiff("day",[ORDER DATE],[DELIVERY DATE])


    However, this calculation includes all days, not just weekdays.


    Suppose we want to output the number of weekdays (not Saturday or Sunday)? We can use a more complicated expression to calculate only days in between the two dates that are Monday through Friday:


    ((((Integer(((Integer(DateDiff("day",[ORDER DATE],[DELIVERY DATE]) / 7) * 5) + DayOfWeek([DELIVERY DATE])) - DayOfWeek([ORDER DATE])) + (if((integer(DayOfWeek([ORDER DATE])))>(integer(DayOfWeek([DELIVERY DATE]))),5,0))) - (if((integer(DayOfWeek([DELIVERY DATE])))=(6),1,0))) + (if((integer(DayOfWeek([DELIVERY DATE])))<>(0),1,0))) + (if((integer(DayOfWeek([DELIVERY DATE])))=(0),1,0))) - (if((integer(DayOfWeek([ORDER DATE])))=(0),1,0))


    If you have a non-standard work week (like you also include Saturdays), you can update the expression above to include Saturdays and only exclude Sundays.


    This of course will not pick up holidays that fall into the work week.  If you want to include this, there are a couple of workarounds that are not ideal , but will do the trick. One such workaround is to create an if statement for each holiday you want to include, and check if the holiday is between the ‘ORDER DATE’ and ‘DELIVERY DATE’. Then sum up all of the if statements and you will have a list of holidays that fell between the two dates.  You can subtract this from the first column you created and you have your working days that exclude holidays (assuming you only include the holidays that fall on working days)…I did say it was a workaround after all.


    As an example, suppose we wanted to exclude Christmas and Easter. In 2009, Christmas was the 359th day of the year and Easter was the 102nd  day of the year. We can then create another calculated column  that gives us the total number of holidays falling on weekdays between our two dates, using the following expression:


    sum(if (DayOfYear([ORDER DATE]) < 359 and 359 < DayOfYear([DELIVERY DATE]),1,0),if (DayOfYear([ORDER DATE]) < 102 and 102 < DayOfYear([DELIVERY DATE]),1,0))


    Of course ,maintaining a hard-coded list of holidays inside your calculation may not be the most effective solution. If you want a more robust solution that allows the user to enter the holidays in , then we would recommend using our SDK to either create a tool that allows the user to configure a working week and specify holidays, or create a calculation that shows up in the function list inside any expression dialog. Similar to the tool, the calculation would take in as arguments, the two date columns, and then a list of strings defining the work week (i.e. ‘M,T,W,Th,F’), and also a listing of holidays (‘Apr-12-2009, Dec-25-2009,etc…’)


  • Ranking Over Groups

    One common question we hear all the time in training is how a person can rank something over various groups in their dataset. The answer to this depends on your groups and how they are constructed. Ill explain by using two examples.  

    First, let’s assume your groups are subjects for a film.  


    Since all the records in each desired group (subject) have the same values, we can use the rank function inside our expression language and build a calculated column to define the rank per subject. In this case, let’s assume we want to rank the length of each film in each subject, in descending order.

    The Rank function takes in one mandatory argument and two optional arguments.  The mandatory argument is the column you wish to rank (in this case the Length column). The next argument, which is optional, defines the sort order. The default value is ascending “asc”, and if you want to sort in descending order you use “desc”.  The final argument, also optional, defines a column you want to group  the rankings by. In this case, we want to group based off the subject column.

    This works fine and the length of the movies in each subject are ranked independently of movies in other subjects.  But what if we wanted to group the rankings based off something that does not have unique values, like the Subject column does?  Well, the best answer is to create a new column that does create a unique value for each group.  The example we will show below is using a Date column where we want to group by each month in each year.

    In the data shown below, each value in the date column is unique.  


    We can create an intermediary calculated column to create a value per month/year.   To do this, we can retrieve the Year from each date, multiply it by 100, and then add the numeric representation of the Month to it (1 for Jan ,2 for Feb, etc…). Finally we cast it all as an integer and we get a Column which has the same value for all rows of the same month and year.



    You can then use the UID column as the grouping column inside the Rank function:


  • Adding Hyperlinks into Tables in TIBCO Spotfire

    Sometimes you would like to link certain values in your document to external information, maybe a google search, maybe a database that pulls back images or video, etc… You can always create a hyperlink inside of a Text Area to goto an external website, but there is no way to pass in specific cell values.  This is where the Link Renderer for a Table visualization comes in handy.

    Imagine you have a Table included in your Analysis file , which shows a list of films from 1920 to 1997, as shown below.


    You may want to link this analysis to some outside source, say the Internet Movie Database ( so that if the user wants more information on one of the films, they can be redirected to that webpage passing in a value from one of the cells as a key.

    To do this, you need to open the properties dialog for the table and click on the Column section, as shown below:


    Then in the ‘Selected columns:’ pane, select the column you wish to apply the hyperlink to and change the ‘Renderer’ option from Text to Link.  You then need to click the ‘Settings’ button to configure it.

    In here, you need to specify the URL that you want to connect to and you can use the {$} keyword wherever you want to enter the cells value.  For the IMBD URL, there is a query parameter called q which allows you to pass in the title.  An example of this is shown below:


    Once you click ‘OK’, each cell in the Title column inside your Table becomes a hyperlink:


    Interested in learning how to become an expert Spotfire Business Author? Enroll in our winter bootcamps now. Bootcamps are an intensive 8 week learning program, using a unique blended delivery model. Students get weekly notes and training modules available to consume via our training portal at their own convenience, and this is complemented with course forums and also 3 live 2-hour webinars throughout the course.  Details are available at:

    If you have any questions, please contact us at


  • Using Node Navigation Methods in Calculated Columns

    People always ask us if there is a way to show changes in values from within the same column over different intervals, like weeks or months. For example, what was the sales this week compared to the sales last week, how much did a patient weight this visit compared to last visit, or what was the change in stock price from this quarter compared to last quarter?  You can do this using a custom expression, but then the values are not available to use elsewhere. If you wish to view them elsewhere (like in a table plot), you will need to use a calculated column.  

    Let’s assume we have the following data loaded that displays sales data per week:


    If we plot this on a Line Chart , as shown below, we can see the trend of sales per week.

    We may want to be more specific and show the change in sales from one week to another. For this, we can use the previous Node Navigation Method in a calculated column expression as shown below:

    The result will be a new column called Weekly Change. We can then plot the Weekly Change on a Bar chart as shown below.


    What if your data contains another category you want to group by? Say you have multiple salespeople and you want to look at weekly change per salesperson?

    For this you can use the same expression as before , but also use the Intersect method to say to group by the Salesperson column.

    Once this is done, the newly created column contains the weekly change per salesperson.


    Notice the Weekly Change value for the first row is empty, since there was no previous value to compare it to. You can add methods into the expression to remove the null value with a zero if desired.

    You can also plot this on a Bar Chart using the Trellis feature to Trellis by Salesperson to see their individual weekly change.

    Keep in mind the differences between using calculated columns and custom expressions. If you filter your data, the custom expression will be recalculated, but the calculated column will not be. 

    If you want more information on the Intersect method, the previous method (or any other Node Navigation) methods, please take our SP 141 Computational Analytics course using one of our three delivery options: onsite, regional, or blended. This course will teach you all the core concepts required to understand the Spotfire Expression Language  in either a calculated column or a custom expression.


Other Spotfire Blogs

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