Search code examples
functionspotfiretibco

Spotfire over function with previous and category

For a giving row, I would like to calculate the previous date in the table on the same category. See example below:

date category expected_date
03/02/2023 A
06/02/2023 A 03/02/2023
07/02/2023 A 06/02/2023
03/02/2023 B
04/02/2023 B 03/02/2023
06/02/2023 B 03/02/2023
07/02/2023 B 06/02/2023
03/02/2023 C
07/02/2023 C 03/02/2023

How could I get the expected dates ?

Solution

  • By creating an intermediate node using the rank.

    First calculated column:

    [tmp_rank] = DenseRank([date],[category])

    Second calculated column:

    First([date]) over Intersect(Previous([tmp_rank]),[category])

    Be careful to not directly use Previous([date]) because it is not calculated in the current category but globally for the whole dataset. If categories do not all have the same dates, empty cells will be generated.