cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
RVhydrA
Level III

calculating sum of lag x rows

I am trying to create a column that calculates the sum of the lag 24 rows. In this instance I have a column "Precip" that contains hourly precipitation totals. There is a second column  "precipitation station" denoting from which location each value was recorded. I have sorted the columns based on "DateTime" and the "Precipitation Station". I am looking for an efficient way to sum the previous 24 values in the Precip column thus creating a new column that tells me how much it rained in the previous 24 hours. This 24 hour total is station specific so it cannot calculate the lag 24 values if the lag 24 precipitations do not match. 

1 ACCEPTED SOLUTION

Accepted Solutions
RVhydrA
Level III

Re: calculating sum of lag x rows

I ended up figuring this out on my own, it is a simple summation function. Figured i'd post it in case anyone else needs to do something like this. 

 

 

If( :Precipitation Station == Lag( :Precipitation Station, 1 ),
	Summation( i = 1, 24, Lag( :Precipitation, i ) ),
	:Precipitation
);

 

 

View solution in original post

4 REPLIES 4
pmroz
Super User

Re: calculating sum of lag x rows

If you are trying to calculate the sum of precipitation for a given day/station, create a new column which is just the date part of the datetime.  Then do a tabulation by date and station, and put Precip in the middle.

This program creates an example dataset with a formula column for date and a Tabulate program to calculate the precipitation by date/station.

New Table( "Example", Add Rows( 20 ),
	New Script( "Tabulate", 
		Tabulate(
			Show Control Panel( 0 ),
			Add Table(
				Column Table( Analysis Columns( :Precip ) ),
				Row Table( Grouping Columns( :Precip Date, :Station ) )
			)
		)
	),
	New Column( "Precip Datetime", Numeric, "Continuous", Format( "ddMonyyyy h:m:s", 22, 0 ),
		Input Format( "ddMonyyyy h:m:s", 0 ),
		Set Values(
			[3660710400, 3660714000, 3660717600, 3660721200, 3660724800, 3660710400,
			3660714000, 3660717600, 3660721200, 3660724800, 3660796800, 3660800400,
			3660804000, 3660807600, 3660811200, 3660796800, 3660800400, 3660804000,
			3660807600, 3660811200]
		),
		Set Display Width( 186 )
	),
	New Column( "Station", Character, "Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "A", "A", "A", "A",
			"A", "B", "B", "B", "B", "B"}
		)
	),
	New Column( "Precip", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5, 3, 4, 5, 1, 1, 4, 5, 6, 7, 5, 4, 5, 6, 7, 8] )
	),
	New Column( "Precip Date", Character, "Nominal",
		Formula( Short Date( :Precip Datetime ) )
	)
);
pmroz
Super User

Re: calculating sum of lag x rows

Precip.png

RVhydrA
Level III

Re: calculating sum of lag x rows

I'm not actually trying to compute the total precip for a given station/date, but rather station/previous 24 hour period. so for example if the DateTime for a given for is 1/3/2020 09:00:00 AM I would want to take the previous 24 values in the precip column and sum those. So starting with 1/3/2020 08:00:00AM and going all the way back to 1/2/2020 09:00:00 AM. Essentially a moving 24 hour window of time. This column would thus give me the previous rain over the prior 24 hours for each row in my data table.

RVhydrA
Level III

Re: calculating sum of lag x rows

I ended up figuring this out on my own, it is a simple summation function. Figured i'd post it in case anyone else needs to do something like this. 

 

 

If( :Precipitation Station == Lag( :Precipitation Station, 1 ),
	Summation( i = 1, 24, Lag( :Precipitation, i ) ),
	:Precipitation
);