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

How to calculate moving averages for selected rows

ClusterFerret68

Hello everyone!

I am trying to write a script that will calculate moving averages using selected rows in a table.

To set the stage:

I have a data table (example attached) with multiple executions (Unique ID) of a 3 stage process (A, B, C). There is a reported value for each stage and these values are summed to give a Total value for each occasion. I would like to have a column that reports a moving average of the Total. I've looked through some of the discussions and have gotten some good tips for how to do this (i.e., moving averages for each Unique ID...THIS example helped and the formula is in :Moving Average (Unique ID).

The next step I'd like to do is to ONLY include a subset of the runs in this evaluation. The runs are highlighted in red and the desired result (in this case the moving average of only three occasions) is in :Desired Result.

I'm having trouble with the script to select the subset of runs for evalutaion.

Also - I'm relatively new to JSL and would also welcome suggestions on how to get at the end result in a cleaner or more efficient manner.

Thanks in advance for your help!

Chris

Moving Avg w selected rows.jmp

Accepted answers

jthi

ah.. so you want to calculate col moving average of to include rows but only report it on the last row of each unique ID?

Note that this will calculate the moving average using ALL values in the groups, not just the last values but it will report it on the last line (remove the if-statement to see what it really does)

If(:To Include == "Yes" & Row() == Col Max(Row(), :Unique ID),
	Col Moving Average(:"Total (Sum of Stages for each Unique ID)"n, 1, :To Include),
	.
)

You can also perform the calculation based only the last rows but the calculation will keep getting slower and more difficult to read

If(:To Include == "Yes" & Row() == Col Max(Row(), :Unique ID),
	Col Moving Average(
		If(Row() == Col Max(Row(), :Unique ID),
			:"Total (Sum of Stages for each Unique ID)"n,
			.
		),
		1,
		:To Include
	),
	.
)

These are easiest to understand when you break it into many separate formulas

All comments

jthi

What is the selection of those three rows based on?

ClusterFerret68

Jarmo,

This process is run in two different settings: one setting is for development use and the other for "official" use (if that makes sense).

and those three lines represent the latter. For the moving average I don't want to include the development runs. However, I DO want to include all data in the tracking/trending sheet.so I have a "single source of truth" representing all settings/operators/etc. The attached sheet just represents a minimal set of info to help address the Ask for the JMP community.

I've been building similar datasets over the years and have just recently started to work with JSL to try to develop more efficient workstreams. I don't have any coding background, though, so I'm still on a steep learning curve. This group has been really helpful!

Chris

jthi

I'm just wondering how to pick those correct rows of interest? Is it done based on color or cells in specific columns? Do you select the rows manually and then the moving average is calculated from those selected rows?

For example you could gave some grouping column and then utilize that

If(!Is Missing(:Group),
	Col Moving Average(As Column("Total (Sum of Stages for each Unique ID)"), 1, :Group),
	.
)

You could also create a subset of just those rows (maybe linked one) to make the calculation even simpler (Edit: this won't work with linked subset even though it looks like it does in preview)

Col Moving Average(As Column("Total (Sum of Stages for each Unique ID)"), 1)

Col Moving Average(name, options, <By var, ...>) can be good read as it is one of the most confusing formula functions

ClusterFerret68

Ahh...now I understand your question.

I was thinking to select rows where :To Include == Yes. Is there a better way?

I'll read that documentation...I have been confused by the function, for sure!

Chris