How to sort a date column in descending order in a Cross Table

How to sort a date column in descending order in a Cross Table

When you create a Cross Table with a date column on an axis, you will notice that the dates are in ascending order and there is no way to change the sort order. This is because date columns only have one natural sort order in Spotfire which is ascending, and so dates will always be in the order oldest to newest.

There are two existing existing enhancement requests that are related to this:

• SPF-I-680: Reverse Sort order option for date columns
• SPF-I-4953: Configurable sorting in cross table headers

This article explains two methods available now of sorting from newest to oldest in a cross table. Option 1: Sort on a custom column
Instead of sorting the date column directly, create a new calculated sort column, and use it as on the vertical axis before the date column.

For example, say you have a [Date] column, and you use it on the cross table vertical axis, this results in an expression like:

<[Date]>

Edit the axis expression by adding in a rank expression, like:

DenseRank([Date],"desc") as [Index]

resulting in:

<DenseRank([Date],"desc") as [Index] NEST [Date]>

See the attached sample dxp file page 1 for an example.


Option 2: Use IronPython to change custom sort order for the date column.

This can also be done with an Iron Python script based on this example in the TIBCO Community Wiki:

• Use IronPython to Set a Custom Sort Order in TIBCO Spotfire®

Note: When new data is added in, you need to reconfigure the custom sort order so this might not be a good solution for a large data sets or a large number of data tables or any data that will change regularly.

See the attached sample dxp file page 2 for an example.

  Idea: Reverse Sort order option for date columns

• https://ideas.tibco.com/ideas/SPF-I-680

Idea: Configurable sorting in cross table headers

• https://ideas.tibco.com/ideas/SPF-I-4953

Wiki: Use IronPython to Set a Custom Sort Order in TIBCO Spotfire®

• https://community.tibco.com/wiki/use-ironpython-set-custom-sort-order-tibco-spotfire