calculation the difference for same column for the specific rows in Spotfire

Question

I have a problem about the difference calculation for the rows using calculated column in Spotfire.

I am wondering if it is possible to create a calculated column that will count the difference between the current row with the next row which have different attribute. A sample table could be like this:

the result could be like this:

the basic row is:

1. When type=1, then calculate the difference between its current value and its next nearest row which type=0, then add the result to a new calculated column.
2. btw, the VALUE is always increase :)
3. for example, for the first result 2, current value is 20, next row is the nearest type with 0, and the value of next row is 22, then the result is 2
4. but for the next type=1, the current value is 25, and its nearest type=0 is on the sixth row, so the result could be 29-25=4

my method which i have tried:

1. i added a new RowID column

2. then try the code:

   if([type]=1),[value] - Sum([value]) OVER (PreviousPeriod([RowID])),null)
   

but it just show the difference between type 1, no type 1 and type 0 :(

Any help or suggestion would be greatly appreciated:)

thanks!

Answer 1

1. Insert a column RowId() and name it RowNum
2. Insert a column with this expression :

([value] - first([value]) over (intersect(previous([type]),AllNext([RowNum])))) * -1

Here is what it will look like. I named the column t1. You can also ignore the Val column:

Explanation:

The trick here is to limit the values in the OVER clause to those that will come after the current row. Furthermore, we want to get the first, next available value which meets our criteria. So, we take the first value, first([value]), that has it's previous [type].This always be 0 since there isn't any negative values for [type], thus this limits the rows we are working with to those with [type] = 1 by using previous([type]). Now, to limit it to only the rows that come after the current row, we use AllNext([RowNum]). The Intersect statement states take the value where both of these rules are met. So looking at RowNum = 4 it is evaluated like this.

[value] = 25
Previous([type])= 0 since current type is 1
AllNext([RowNum]) = RowNum that is > our RowNum which is 4, so tow numbers 5 - 7
The First([Value]) that meets these criteria is in RowNum = 6, which is 29 since it has [Type] = 0 and it's RowNum is > 4
Note, Row 7 also meets this criteria but it isn't the First() one.
Now, do the math... 25-29 = -4, and since you said the values always increase, we just multiply by -1 to get it in the format you wanted

Answer 2

@ZAWD - Another way of solving this:

Step 1: Inserted a calculated column 'RowID' using the expression RowId()

Step 2: Inserted a calculated column 'test0' using the expression below

sum([Value]) over (Intersect(next([RowID]),Previous([Type])))

Step 3: Inserted a calculated column 'test' using the expression below

[Value] - sum([test0]) over (Next([RowID]))

Step 4: Inserted a calculated column 'myresult' using the expression below

Abs(If((Sum([Type]) over ([RowID])=1) and (Sum([Type]) over (Next([RowID]))=1),[test],[Value] - [test0]))

Note: 'test0' and 'test' columns run in the background. They need not be included in the main table

Final table looks like below:

Also, this solution works fine in whichever order the values are in. I have tested this solution with different scenarios and seems to be working fine.