Spotfire Custom expression in cross table providing unreliable results

[Susan Wallenslager]

I am trying to flag rows that meet a failure condition by using a custom expression in a Spotfire cross table.

A row should be flagged as a failure if the following three conditions are true:

1. TheAsset_System_ID of the row matches theAsset_System_ID of the previous row

2. TheCHU_Problem_Code of the row matches theCHU_Problem_Code of the previous row

3. The difference between theSR_Open_Date of the row and the previous row is less than or equal to 10 days.

This needs to work regardless of the sort order or the physical order of the data.

The following custom expression returns the correct result most of the time:

case

when First(DateDiff("day",First([sR_Open_Date]) OVER (Intersect([Asset_System_ID],Previous([Axis.Rows]))),[sR_Open_Date]) is not null) AND First(DateDiff("day",First([sR_Open_Date]) OVER (Intersect([CHU_Problem_Code],Previous([Axis.Rows]))),[sR_Open_Date]) is not null) AND First(DateDiff("day",First([sR_Open_Date]) OVER (Intersect([Asset_System_ID],Previous([Axis.Rows]))),[sR_Open_Date])