nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

calculating sum of lag x rows

RVhydrA

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.

Accepted answers

RVhydrA

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
);

All comments

pmroz

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

RVhydrA

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

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
);