Search code examples
calculated-columnsdifferencespotfire

Spotfire: Calculate delta between two filtered series

I must calculate a delta between two time series, based on filtering over one column.

Here's the situation, I've got a table like this:

table

The user chooses a reference Id with a dropdown list, and the string is stored in document property ReferenceId.

I want a calculated column that will dynamically calculate the difference between the current row value and the corresponding reference row value. For instance, if ReferenceId=Id1:

table2

How can I do that? Thanks in advance.

Solution

  • You'll need to reference year to get the correct value... This should work

    100*([Value]-First(case when "${ReferenceId}"=[Id1] then [Value] end) over [Year])/First(case when "${ReferenceId}"=[Id1] then [Value] end) over [Year]