calculate the total value for each group using Calculated Column in Spotfire

Question

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

For example, the raw data is as below, the raw table is order by id, for each type, the sequence is 2,3,0.

id       type value state
 1        1    12   2
 2        1    7    3
 3        1    10   0
 4        2    11   2
 5        2    6    3
 6        3    9    0
 7        3    7    2
 8        3    5    3
 9        2    9    0
 10       1    7    2
 11       1    3    3
 12       1    2    0

for type of each cycle of (2,3,0), I want to sum the value, then the result could be:

 id       type value state  cycle time 
 1        1    12   2
 2        1    7    3
 3        1    10   0        29
 4        2    11   2
 5        2    6    3
 6        3    7    2             
 7        3    5    3
 8        3    9    0        21
 9        2    9    0        26
 10       2    7    2
 11       2    3    3
 12       2    2    0        12

note: only the row which its state is 0 will have the sum value , i think it will be easier to see the rules, when we order the type :

 id       type value state  cycle time 
 1        1    12   2
 2        1    7    3
 3        1    10   0        29
 4        2    11   2
 5        2    6    3
 9        2    9    0        26
 10       2    7    2
 11       2    3    3
 12       2    2    0        12
 6        3    7    2             
 7        3    5    3
 8        3    9    0        21

thanks for your time and help!

Answer 1

Here is a solution for you.

1. Insert a Calculated Column RowId() and name it RowId
2. Insert a Calculated Column If(Mod([RowId],3)=0,[RowId] / 3,Ceiling([RowId] / 3)) and name it Groups
3. Insert a Calculated Column Sum([value]) OVER ([Groups]) and name it Running Sum
4. Insert a Calculated Column If([state] = 0,[RunningSum]) and name it OnlyState=0

The only thing to really explain here is #2. With the data sorted as you listed in your example, the last row for each group, based on the RowId, should be divisible by 3. We have to do it this way since your type field can have multiple groups for any given type. RowId 3, 6, 9, 12 etc will all have a Modulus of 0 since they are divisible by 3. This marks the last row in each set. If it is the last row, we just set it to RowId / 3. This gives us groups 1,2,3,4 etc... For the rows which aren't divisible by 3, we round them up to the nearest whole number of the divisor... which will be the last row in the set.

The last calculated column is the only way I know how to get ONLY the values you care about. If you use the If [state] = 0 logic anywhere else, you negate all other rows.