cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
Stewart
Level I

How to script a rolling count using Table Summary?

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

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

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

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

jthi_0-1685037323226.png

If you have multiple years it will require modifications

-Jarmo

View solution in original post

3 REPLIES 3
jthi
Super User

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

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.

-Jarmo
Stewart
Level I

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

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.

jthi
Super User

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

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

jthi_0-1685037323226.png

If you have multiple years it will require modifications

-Jarmo