topic Re: Issue with creating a rolling cumulative sum as a function of time in Discussions

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

Sburel — Sat, 10 Jun 2023 23:34:09 GMT

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

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

jthi — Tue, 20 Jul 2021 17:54:17 GMT

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?

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

Sburel — Tue, 20 Jul 2021 20:01:28 GMT

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

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

brady_brady — Tue, 20 Jul 2021 20:52:44 GMT

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

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

Sburel — Wed, 21 Jul 2021 14:46:45 GMT

Hi Brady,

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

Best,

Sebastien