Forum Discussion
Calculating difference between consecutive rows
Calculating difference between ROWS
Hello,
Could you please help me with advice how I could calculate the value difference between two rows?
In the table below:
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 |
|
|
As shown in the sample data, I have many “Problem ID”, and cumulative values of column “NumVal” I have to do extraction based on problem Field id and Problem ID column.
For instance
Problem ID 6A6C4C2A-7328-4A91-B598-6F629C905A86 = NumVal(ProblemFieldID=4 ) - NumVal(ProblemFieldID=7 ) will give me the temperature of that problem
The data is in no particular order, and I would like to be able to find the temperature for particular problemID by calculating the difference between two consecutive rows.
Br,
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.
7 Replies
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?
