Issue with creating a rolling cumulative sum as a function of time

[#1]

Hello,

I have a simple example where we do a kinetic ( in column "time (min)") for a bunch of samples ( in column "variable x"). I need to create a new column that run a cumulative sum that accounts for the time elapsed for each one of the samples listed in "variable x". I tried the col cumulative sum function but it does not do accomplish the goal.

I suspect a good approach would be with row, lag, or offset but I have no clue how to put that together. I've attached an

names default to here(1);

New Table( "noci",
	Add Rows( 8 ),
	New Column( "variable X",
		Character,
		"Nominal",
		Set Values( {"a", "a", "a", "a", "b", "b", "b", "b"} )
	),
	New Column( "Time (min)",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 1, 2, 3, 4] )
	),
	New Column( "Nociception Data",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 1, 2, 3, 1, 2, 1] )
	),
	New Column( "Cumulative nociception as function of ellapse time",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 3, 4, 6, 3, 4, 6, 7] )
	)
)

example data table

Any input to put me on the right track would be very appreciated!

Best

Sebastien

[#2]

How come Col Cumulative Sum() doesn't accomplish the goal? If the "Cumulative nociception as function of ellapse time" has the values you want, col cumulative sum seems to work.

Col Cumulative Sum(:Nociception Data, :variable X)

I might have also missed or understood something incorrectly and you want something different, if that is the case, could you provide a column with correct results?

[#3]

Hi @jthi,

You are correct but the problem I have is that if the table gets reordered (say by reordering on the basis of the nociception data column), using the cumulative column will not use the time (min) column to calculate the cumulative sum.

Hopefully, I'm being clear.

Best

Sebastien

[#4 · Accepted solution]

Hi,

Would it work to do the following?

1) Sort the table by x and time variables

2) Add the cumulative sum formula as jthi shows

3) Delete (or suppress the evaluation of) the formula from the new column

At this point, the cumulative sum column will not change based on table sorting.

Cheers,

Brady

[#5]

Hi Brady,

Not the most elegant solution but it would work. Still I wish I would have thought of it

Best,

Sebastien