In TIBCO Spotfire version 3.2, there were some updates to Cross Tables, which allowed users to add subtotals for each level of a given hierarchy. In TIBCO Spotfire version 3.3, more updates were released to enhance and extend this functionality.
In 3.2 the subtotals were always applied to every parent level of a hierarchy. Now, starting in 3.3, you can specify which level of the Cross Table hierarchy should have subtotals applied. This is done in the Properties dialog for the Cross Table under the ‘Column subtotals’ section.
You can also now specify whether the subtotals should be displayed before or after the axis values.
In addition to this update, Spotfire now supports showing only the top or bottom N values in a specific Cross Table grouping. This is done in the Properties dialog for the Cross Table under the ‘Sorting’ section.
There is a checkbox labeled ‘Show only top/bottom’ and we can set that option and the specific number for both columns and rows in the Cross Table. There is also a checkbox to specify whether there should be an indication that some rows or columns are hidden.
For example, if our data has 20 rows of values for a specific grouping that are displayed by default in the Cross Table, and we set the option to only show the top 5, and also check the ‘Indicate hidden rows’ value, than only the top 5 will be shown and then a cell will be added with ‘ …’ in it representing that there is additional data which is currently hidden.
Specifying whether the rows or columns should be in ‘Ascending’ or ‘Descending’ order will determine if the top or bottom N values are displayed.
In addition, you can use the bookmark features to capture different states of the visualization properties to expose different options for the user via property controls. For example, you can create bookmarks and then Action Controls which will toggle subtotals to be on top or bottom, as shown below:
The option for specifying the number of rows or columns to display does not take in expressions, so there is no way to use property controls to alter these values directly, but you can do it with a bit of scripting and property controls. The following script will take in two parameters, cTable which refers to the specific Cross Table and intTopNRows, which refers to a property for setting the number of rows to show.
from Spotfire.Dxp.Application.Visuals import CrossTablePlot
crossTable = cTable.As[CrossTablePlot]()
crossTable.ShowTopNRows = True
crossTable.TopNRowCount = intTopNRows
