Forum Discussion
Calculating difference between consecutive rows
Probably a pivot-operations is what you're looking for:
Check column "ProblemFieldID" -> Transform -> Pivot Column -> Choose "NumVal" in Value-Field.
This will return a table with 1 additional column per ProblemFieldID which you then can build your calculations on.
When you say no particular order, do you mean that the row you might want to subtract might be 10 previous or 6 previous or is it always the previous row?
Greg_Deckler The rows could be not in the same order / might be 10 previous or 6 previous/.
OK, so then is the pattern that you always want to match problem ID's 4 and 7 or how do you know which earlier row to use?
In the table, in column Problem ID every problem has 2 problemFieldID / one that show problem filed id 4 and one with problem field 7/
What I'm trying to calculate is
Problem id 6A6C4C2A-7328-4A91-B598-6F629C905A86 temprature = NumVal/Problem Field Id=4/ - (Problem id 4 - problem ID 7).
ProblemID
ProblemFieldID
NumVal
Result
Temperature
6A6C4C2A-7328-4A91-B598-6F629C905A86
4
98
text =(Problem id 4 - problem ID 7)
10
6A6C4C2A-7328-4A91-B598-6F629C905A86
7
88
C8E745BC-7CC2-4220-97DC-503664FF5828
4
230
text =(Problem id 4 - problem ID 7)
43
C8E745BC-7CC2-4220-97DC-503664FF5828
7
187
D814D0FF-3B18-4C51-990D-F6FF4CAE15F1
4
93
text =(Problem id 4 - problem ID 7)
10
D814D0FF-3B18-4C51-990D-F6FF4CAE15F1
7
83
I was thinking that if the table is transpose:
Problem
ProblemID Comp T/ ProblemfieldID 4 Ref T/ ProblemfieldID 7
6A6C4C2A-7328-4A91-B598-6F629C905A86 98 88
I could say formula = Comp T - Ref t.
But I do not know how to transpose or rotate the table that way.
Probably a pivot-operations is what you're looking for:
Check column "ProblemFieldID" -> Transform -> Pivot Column -> Choose "NumVal" in Value-Field.
This will return a table with 1 additional column per ProblemFieldID which you then can build your calculations on.