用另一列spotfire中的先前值填充列

Question

我正在尝试插入一个计算列，以便在T1 = CMP 1 Stops时复制timestamp，而在T1 = CMP 1 starts'时复制

timestamp         T1             Calculated           Expected
5/1/2017 14:00          
5/1/2017 14:15          
5/1/2017 14:30  CMP 1 Starts        
5/1/2017 14:45  CMP 1 Stops     5/1/2017 14:30      5/1/2017 14:30
5/1/2017 15:00          
5/1/2017 15:15          
5/1/2017 15:30          
5/1/2017 15:45          
5/1/2017 16:00          
5/1/2017 16:15          
5/1/2017 16:30  CMP 1 Starts        
5/1/2017 16:45  CMP 1 ON        
5/1/2017 17:00  CMP 1 Stops      5/1/2017 16:45     5/1/2017 16:30
5/1/2017 17:15          
5/1/2017 17:30          
5/1/2017 17:45          
5/1/2017 18:00          
5/1/2017 18:15          
5/1/2017 18:30          
5/1/2017 18:45  CMP 1 Starts        
5/1/2017 19:00  CMP 1 ON        
5/1/2017 19:15  CMP 1 Stops       5/1/2017 19:00    5/1/2017 18:45
5/1/2017 19:30          
5/1/2017 19:45          

示例：Expected列

注意:没有必要在T1=CMP 1 Stops时填充相同的行，即使它在T1=CMP 1 Starts时用值填充所有的null values，它也适用于我

Answer 1

您需要的第一个表达式是：

If((Trim([T1])="CMP 1 Stops") or (Trim([T1])="CMP 1 Starts"),Max([timestamp]) over (PreviousPeriod([timestamp]))) as [YourNewColumn]

然后，如果您想将其限制到T1 =“CMP1 Stops”的行，只需添加另一个计算列：

case when [T1] = "CMP 1 Stops" then [YourNewColumn] end as [YourFinalColumn]