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!

Loan

does VALUE always increase as new rows are added or can it decrease as well – Python

Hi, the VALUE is always increase :) – Loan

Thanks for the information @Zawd – Python

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

Python

I'm glad I could help @Loan OVER functions were one of Spotfires most difficult Expressions for me to learn so I try to be descriptive! – Python

I am really new for it, i supposed i can learn it faster, but quite difficult to understand the syntax in a short time – Loan

hi, i just posted a new question about it, which i thought would be quite similar, pls refer to the #39725848, when you have interest :) – Loan

i have found the main solutions for it, but has a small error :( – Loan

Nice @Loan i'm glad you found it! – Python

i will look at your other question @Loan – Python

thanks for it :) actually the solution is based on your idea which you have posted for this question :) – Loan

looks good, @scsimon! OVERs are a pain, even to explain to someone once you understand them, so nice job! :) – Composition

@Loan since you liked the answer could you award me the bounty before it expires? I also just answered your other question. You have some tough ones! Cheers! – Python

Hi, thanks a lot for it, I thought i have award the bounty at the first day when u posted the answer :) – Loan

sorry, that i have checked it again ,so now, it could be awarded :) thanks agian – Loan

Hey no worries @Loan you have some fun, and very challenging questions – Python

thanks for understanding, have no idea for the bounty rules. But this question have frustrated me these days...:(, will try your idea tmr :) – Loan

Ok let me know how it goes for you @Loan – Python

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.

Hellhole