First() and Last() functions return unexpected results. – Revvity Signals

Date Posted: 2018-06-26
Product: TIBCO Spotfire®

Problem:

First() and Last() functions return unexpected results.

Solution:

The First() and Last() functions return unexpected results which do not appear to correspond to the first and last values in the data, or the results from the First() and Last() functions have changed after applying a hotfix.

Explanation:

Physical Order of the Rows of Data

The Help documentation correctly describes the First and Last functions:
- "First returns the first valid value based on the physical order of the rows of data in the selected column."
- "Last returns the last valid value based on the physical order of the rows of data in the selected column."

But the inline function descriptions have this incomplete description, which does not mention the physical order of data:
- "Returns the first valid value."

The physical order that is used in these functions is visible in the result returned by the RowId() function, which is not affected by filtering or sorting. So it is the physical order of your data as it is imported into Spotfire that affects the results of the First() and Last() functions, not any sorted order that you may see displayed within Spotfire.

Defect in Versions 5.5, 6.0, and 6.5

In addition, there was a defect present in Spotfire versions 5.5, 6.0, and 6.5, which has been corrected and works as intended in version 7.0 and later.

First and Last methods with navigations AllPrevious, AllNext and LastPeriods, such as First(Value) OVER AllPrevious(Category), should be calculated on the original data table row-ordering but was sometimes incorrectly calculated on the sorted result from the navigation instead.

Some aggregations (EXCLUDING: Sum, Product, Count, Min, Max, First, Last, Avg, Range, Percent) with a column method (such as Rank or DenseRank) as argument may have given incorrect results if combined with certain OVER expression navigations. For example: Q3(Rank(Column)) OVER Next(Column) .

For the affected earlier versions, the following hotfixes (available from http://support.spotfire.com/patches_spotfire.asp) corrected this behavior:
- TS 6.5.3 HF-015
- TS 6.0.3 HF-020
- TS 5.5.2 HF-023

These hotfixes will change the results of any expressions that relied upon the earlier incorrect behavior. To maintain the earlier behavior after upgrading Spotfire, these expressions will need to be updated per the Resolution below.

Option 1:

If the sorted result from the navigation is wanted, then ValueForMax(Category, Value) OVER AllPrevious(Category) can be used instead, or LastValueForMax() can be used here if Last() was used before the upgrade.

Option 2:

While ValueForMax does exactly what is required. It has not been optimized for large hierarchies and might not have optimal performance, especially on larger hierarchies. Even if the amount of data is pretty small, the rank hierarchy with all previous is quite large. There is a workaround however that can be done using two calculated columns instead. First create a calculated column called "rankToUse" with the following definition or similar:

Max(CASE WHEN [mycolValue] IS NOT NULL THEN [Rank] ELSE NULL END) OVER (AllPrevious([Rank]))

This column will calculate the rank to use for a specific row by getting the largest rank where the mycolValue column is not null. In order to get the actual mycolValue value we need to add a second calculated column that will give the expected result:

Max(CASE  WHEN [Rank]=[rankToUse] THEN [mycolValue] END) OVER ([rankToUse])

This is a fast way to compute the requested results like the earlier ValueForMax() method.

Documentation: TIBCO Spotfire Analyst Functions Overview:
- https://docs.tibco.com/pub/sfire-analyst/7.9.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_functions_overview.htm