Puzzler: derived column from two columns

We have a table with two columns

step time ! Step
0 ! 1
20 ! 1
60 ! 1
0 ! 2
3 ! 2
4 ! 2

Ask you can see, when step increases, the step time goes back to 0.

We want a computed column which would be the cumulative time across all steps.

Thus the values would be:

0
20
60
60
63
64

Is there a formula for that?

The “Running total” action does exactly that in the “Cumulative sum” aggregation mode.

I am not getting the same values

image

Relative time (s) ! Relative time (ss) !Step time (s) !Step !Cycle

Relative time (s) Relative time (ss) Step time (s) Step Cycle
0 0 0 1 1
60 60 60 1 1
60 60 0 2 1
63 63 3 2 1
64 67 4 2 1
65 72 5 2 1
66 78 6 2 1
68 86 8 2 1
69 95 9 2 1
72 107 12 2 1
76 123 16 2 1
82 145 22 2 1
93 178 33 2 1
104 222 44 2 1
115 277 55 2 1
122 339 62 2 1
129 408 69 2 1
135 483 75 2 1
142 565 82 2 1
149 654 89 2 1
157 751 97 2 1
166 857 106 2 1
175 972 115 2 1
184 1096 124 2 1
193 1229 133 2 1
200 1369 140 2 1
208 1517 148 2 1
217 1674 157 2 1

Sorry, I didn’t notice that accumulation happens on the group level for the max value in a step. So times in each step are added in top of cumulative max values of all previous steps. Therefore we need to:

  1. Get max values of each step
  2. Get a cumulative sum of them
  3. Bring them to the next step
  4. Add step times to the cumulative sum

Here is an example that does group-level accumulation.

group-cumulative-sum.morph (4.4 KB)

What you have is great. But the engineers now through me a curve ball. There is both a Step and Cycle, and the step time resets based on both. A test has multiple steps, and the steps restart after a cycle). But the step number does not necessary go back to 1.

(this is an artifact of the test equipment which is built in China, and the excel table is output by it).

I am appending the file, without the voltage values, but just the step time, and what they want for the Relative time (it won’t be in the source data) as well as the step and cycle.

SampleData.csv (31.8 KB)

Then your groups are now defined by both step and cycle. If you understood my solution, you should be able to modify it to accommodate cycles too. If you didn’t understand it, let me know what causes questions.

I thought I got it.

So what I tried on my own was adding a second column to group-by in the aggragate, and then doing the Match based on both step and cycle.

Not really what we want.

Seems to break down at this point on the left-join.

Then again, I think I may have to go back to the Material Scientists. I think they may have given me bad data (I don’t see step time (s) after cycle 3, step 3.