topic How to script a rolling count using Table Summary? in Discussions

How to script a rolling count using Table Summary?

Stewart — Fri, 09 Jun 2023 16:10:41 GMT

I am trying to find a way to summarize a table over a rolling 3 month period. I’ve tried different approaches with the ‘lag’ and ‘col rolling’ functions but haven’t had success yet. Any advice? I am using JMP 16

Example original data

Month	Serial # of unit tested
Jan	1001
Jan	1001
Jan	1002
Feb	1002
Feb	1003
Feb	1004
Mar	1005
Mar	1006
Apr	1006
Apr	1006

Desired summarized data

Month	# of tests, prior 3 months	# of unique SN tested, prior 3 months
Jan	3	2
Feb	6	4
Mar	8	6
Apr	7	5

Re: How to script a rolling count using Table Summary?

jthi — Thu, 25 May 2023 04:59:54 GMT

I'm not sure how those values are calculated. We can only use Apr as it has three months before it, but shouldn't number of tests be 8 and number of unique serial numbers 6?

Most likely it will be easiest to first create Summary table and then perform the calculations there as you will have only one row for each month.

Re: How to script a rolling count using Table Summary?

Stewart — Thu, 25 May 2023 14:42:52 GMT

Sorry, showing results for Jan and Feb was probably confusing. And I didn't explain "prior 3 months" very well. This is what I mean: For the April summary row, count all the tests ran in Feb, Mar, and April and count all the unique serial numbers for the same time period. The March summary row would be similar, but use Jan, Feb, and March. I would accept either blanks for Jan and Feb summary, or Jan based on only Jan data and Feb summary based on only Jan and Feb data.

The key point is that some serial numbers are tested multiple times in different months and if I limit the time frame to just 1 month then the # of tests vs # of unique units tested is not accurate.

Re: How to script a rolling count using Table Summary?

jthi — Thu, 25 May 2023 17:55:49 GMT

This is definitely not the only way of doing this, but one possible option

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Month",
		Character,
		"Nominal",
		Set Values({"Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Mar", "Mar", "Apr", "Apr"})
	),
	New Column("Serial # of unit tested",
		Numeric,
		"Nominal",
		Format("Best", 12),
		Set Values([1001, 1001, 1002, 1002, 1003, 1004, 1005, 1006, 1006, 1006])
	)
);

dt_results = New Table("Untitled 3",
	Compress File When Saved(1),
	New Column("Month", Character, "Nominal", Set Values({})),
	New Column("# of tests, prior 3 months", Numeric, "Continuous", Format("Best", 12), Set Values([])),
	New Column("# of unique SN tested, prior 3 months", Numeric, "Continuous", Format("Best", 12), Set Values([]))
);

// 
order = {"Jan", "Feb", "Mar", "Apr"};
Summarize(dt, uniq_months = By(:Month));

For Each({cur_month}, uniq_months,
	cur_month_idx = Contains(order, cur_month);
	months_of_interest = If(cur_month_idx < 3,
		order[1::cur_month_idx];
	,
		order[(cur_month_idx - 2)::cur_month_idx];
	);

	// number of tests is same as number of rows
	rows_of_interest = dt << Get Rows Where(Contains(months_of_interest, :Month));
	nr_of_tests = N Items(rows_of_interest);

	// number of unique items we can get with associative array
	unique_snrs = Associative Array(dt[rows_of_interest, "Serial # of unit tested"]) << get keys;
	nr_of_unique = N Items(unique_snrs);
	
	cur_month_result = Eval List({cur_month, nr_of_tests, nr_of_unique});
	dt_results << Add Row(1);
	dt_results[N Row(dt_results), 0] = cur_month_result;
);

If you have multiple years it will require modifications