Search code examples
spotfire

Referencing the previous row in Spotfire and a value in another column

I'm trying to do something that is very simple to do in Excel, but seems very tricky in Spotfire:

Imagine you have the following table:

    A
1: 10  
2: 15  
3: 20

... and you want to produce the following:

    A   B  
1: 10  10  
2: 15  25
3: 20  45

In other words, add the current value of A to the previous value of B. If this was Excel, the formula in cell B3 would be = A3 + B2... However, I'm not in Excel, I'm using Spotfire... :) Any thoughts?

NB: If it makes any difference to the answer at all, I will need to use the Intersect() function in conjunction with this, as there is a categorical column to factor in as well.

Solution

  • Hope this helps, I have considered the first column as ID

    ID  A   B
1   10  10
2   15  25
3   20  45

    If(Sum([A]) over (AllPrevious([ID])) is null,sum([A]) over ([ID]),Sum([A]) over (AllPrevious([ID])))