How to use previous value in calculated column in current row calculation

[Stephanie K]

Hi, I'm relatively new to spotfire and have searched extensively to try to work out how to make this happen but have come up empty. Does anyone know how to use the previous value in a calculated column to work out the current value I can't think of a way to do this without coming up with the 'cyclic calculations not allowed' error.

Example data below. The column'calculated oil volume' should be = (1 + %oilloss/100) * previous calculated oil volume

Any tips on this would be much appreciated!

 

 

 

Well Name

Rank

% Oil Loss

Initial Oil Volume (L)

Calculated Oil Volume(L)

 

 

Well 1

1

 

2000

2000

 

 

Well 1

2

-10.2

 

1796

 

 

Well 1

3

-5.1

 

1704

 

 

Well 1

4

-30.5

 

1185

 

 

Well 1

5

-2.5

 

1155

 

 

Well 1

6

-15.7

 

974

 

 

Well 3

1

 

2000

2000

 

 

Well 3

2

-8.5

 

1830

[Gaia Paolini]

We can reformulate the problem recursively this way

 

Calc(i+1) = (1+OilLoss(i+1)/100) * Calc(i)

So that

Calc(N) = (1+OilLoss(N)/100)*(1+OilLoss(N-1)/100)* *Calc(0)

Calc(0) = Initial Oil Volume

So try this:

 

Fill all the empty values of %OilLoss with 0 (you can easily do it via the data menu)

For readability create intermediate column [OilLossPlus] as

 

1 + ([%OilLoss] / 100)

 

Calculate the cumulative product of [OilLossPlus] as [OilLossProd]:

 

Product([OilLossPlus]) over (Intersect([WellName],AllPrevious([Rank])))

 

The Intersect(..) is so the product restarts at every WellName and is ordered by Rank

 

Then your calculated column will be:

Round([OilLossProd] * Max([initialOilVolume]) over ([WellName]),0)

 

Where the Max(..) is needed because the [initial Oil Volume] column is only filled once per well.