Subtracting values from a reference value within same column

[Michael Munson]

I work a lot with microscopy data imported via data tables fromColumbus. I'm becoming familiar with working with spotfire but this kind of problem stumps me and I don't know what keywords to start using to try to find a solution.Working with data I often want to normalise data to values from within the same column, for example in this table setup:

 

 

 

Parameter

Time

Value

 

 

A

1

12

 

 

A

2

14

 

 

A

3

16

 

 

B

1

22

 

 

B

2

24

 

 

B

3

26

 

 

C

1

32

 

 

C

2

44

 

 

C

3

56

 

 

D

1

112

 

 

D

2

154

 

 

 

D

 

3

196

 

 

 

In many cases I want Parameter with string 'A' to be my control value. For all other parameters (B,C,D) i want the 'Value' column to be modified by the parameter 'A' value acrossmatching'Time' values. (All B,C,D time =1 should for example have 12 subtracted, time = 2 then 14 subtracted from B,C,D etc).

How can I generate a new data column that would implement the above requirements

Any help is much appreciated!

[Fredrik Rosell]

Hello Michael,

 

You might want to try adding a calculated column with an expression like the following:

[Value] - First(If([Parameter]="A",[Value],Null)) OVER ([Time])

 

 

Reading from the end: For all values with the same Time (e.g. 1) as the current value, take the value of the first one found where the Parameter is A). Subtract that from the current value.

 

Best Regards

Fredrik

[Michael Munson]

This worked perfectly, thanks for the quick response and help!