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

Tip of the Week

June 2013 - Posts

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


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


    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.


    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.


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


    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.

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


    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.



    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.


    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:

    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.





    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 . When prompted you can login using the 'Proceed without an account' button. Also continue to watch our Spotfire Demo page at as we begin to roll out 5.5 feature demos.


Other Spotfire Blogs

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