计算Spotfire中特定行的同一列的差异 [英] calculation the difference for same column for the specific rows in Spotfire

问题描述

我在使用Spotfire中的计算列来计算行的差异时遇到问题。

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:

结果可能是这样的：

基本行是：

1. 类型 = 1时，然后计算其当前值及其下一个最接近的行（类型 = 0），然后将结果添加到新的计算列中。


2. btw，值始终是：）


3. 例如，对于第一个结果 2 ，当前值为20，下一行是最接近的类型，值为0，下一行的值为22，则结果为2


4. ，但对于下一个类型= 1，当前值为25，并且它最接近的type = 0在第六行上，所以结果可能是29-25 = 4

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

我有尝试过：

1. 我添加了新的RowID列



2. 然后尝试代码：

1. i added a new RowID column

2. then try the code:

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

但它只显示类型1，没有类型1和类型0之间的区别：(

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

任何帮助或建议将不胜感激：)

Any help or suggestion would be greatly appreciated:)

谢谢！

推荐答案

1. 插入列 RowId（）并将其命名为 RowNum


2. 插入带有th的列是表达式：

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

（[value]-first（[value]）over（intersect（previous（[类型]），AllNext（[RowNum]））））* -1

这里是它的样子。我将列命名为 t1 。您也可以忽略 Val 列：

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

说明：

此处的窍门是限制中的值OVER 子句表示将在当前行之后出现的子句。此外，我们希望获得符合我们标准的第一个，下一个可用值。因此，我们取第一个值 first（[value]），它是先前的 [type] 。始终为0，因为 [type] 没有负值，因此这将我们使用的行限制为 [type]的行通过使用 previous（[type]） = 1 。现在，要将其限制为仅在当前行之后的行，我们使用 AllNext（[RowNum]）。 Intersect 语句状态使用满足这两个规则的值。因此，查看 RowNum = 4 时，其评估结果如下。

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

这篇关于计算Spotfire中特定行的同一列的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！