Search code examples
spotfire

Spotfire Date Ranks Incorrectly

Really odd issue here- I've created a calculated column for a RowID purpose that is DenseRank([STRT DT], "ASC", [Prprty]. The goal of this calculated column is to rank the date as time goes on. This has worked perfectly for all instances I've checked up until now. The STRT DT column remains consistent but somehow, the count fails about midway through the dataset.

I've attached two images showing my data- first image is the correct ranking up to row 46 (highlighted where the off count starts in blue), and second image shows the rows that are being counted continuous in the ranking system, but aren't being ranked correctly. Has this happened to anyone and is there a solution/way around something like this?

Image 1 Image 2

Requested Row ID IMG

Solution

  • I think you are still using a string. If the data type of the [STR DT] column is not DateTime, you will get RowID ordered as a string, with 3/4/2023 coming before 3/10/2023, as you are experiencing. To work around this, I tried it with the earlier snippet of data you provided, leaving [STR DT] as a string, and defining the RowID column as

    DenseRank(DateTime([STR DT]),"ASC",[Prprty])

    and it worked for me.