topic How to filter to show last n items/values? in Discussions

How to filter to show last n items/values?

markschahl — Wed, 23 Jul 2025 05:12:48 GMT

Let's use the Semiconductor Capability sample data table for context.

Column lot_id has 13 levels.
Say I want to IMR control chart the last lot only or only the last 5 lots in the data table. Is there an elegant way to do this kind of last n filtering other than manually scrolling through the list of lot_id and manually picking the last 5 lots?

Alternatively, I have years of process sensor data in my data table. I only want to plot the last six months worth.

Re: How to filter to show last n items/values?

jthi — Wed, 23 Jul 2025 05:30:59 GMT

For the second one you can use data filter

You might want to create YEAR-MONTH column to make selection slightly easier

For the first one, is the order only based on row order of the data table? For semiconductor capability they are in alphabetical order, so you could use data filter.

Re: How to filter to show last n items/values?

Nita_Natchanok — Wed, 23 Jul 2025 08:38:26 GMT

I would use these to create categorical column to label latest xx days, months, year and use it in the data filter.

If( :Date >= Date DMY( 1, Month( Col Max( :Date ) ), Year( Col Max( :Date ) ) - 1 ),
	"Yes",
	"No"
)

If( Col Max( :Date ) - :Date <= In Days( 30 ),
	"latest 30 days"
)

You may modify this for the latest 6 months

If(
	:Date >= Date DMY( 1, Month( Col Max( :Date ) ), Year( Col Max( :Date ) ) )
	-In Years( 0.5 ),
	"Yes",
	"No"
)

For Labeling the latest X label

I would use this as example of latest 2 lot ID/cycle.

1. Reassign lot or Cycle by number

If( Row() == 1,
	1,
	If( :Start Date == Lag( :Start Date ),
		Lag( :cycle ),
		Lag( :cycle ) + 1
	)
)

or this

If( Row() == 1,
	1,
	If( :lot ID == Lag( :lot ID ),
		Lag( :cycle 2 ),
		Lag( :cycle 2 ) + 1
	)
)

Then, create this formula to label the latest xx ID or Cycle (2 in this example)

If( Col Max( :cycle ) - :cycle < 2,
	"latest 2 cycles"
)

Alternatively, you can modify this script to run on your data table.

However, you need to rerun this script every time the data is updated. Thus, I prefer the one above.

last_no = 2;

// Define your column names
timestampCol = "Start Date"; // Replace with your actual timestamp column name
categoryCol = "lot ID";     // Replace with your actual category column name

// Reference the current data table
dt = Current Data Table();

// Create a summary table with the latest timestamp per category
dtSummary = dt << Summary(
    Group( column(categoryCol) ),
    Max( column(timestampCol) ),
    Link to original data table(0),
    Output Table Name("LatestPerCategory")
);

// Sort the summary table by latest timestamp descending
dtSummary << Sort(
    By( column("Max(" || timestampCol || ")") ),
    Order(Descending),
    Replace Table(1)
);

// Get the top 2 categories
latestCategories = {};
For( i = 1, i <= last_no, i++,
    Insert Into( latestCategories, dtSummary:lot ID[i] )
);

// Add a new column to flag the latest 5 categories
dt << New Column("Is Latest " ||char(last_no),
    Character,
    Formula( If( Contains( latestCategories, :lot ID ), "Yes", "No" ) )
);

// Optional: Close the summary table
Close( dtSummary, NoSave );

Here is how my test data look like

Re: How to filter to show last n items/values?

hogi — Thu, 24 Jul 2025 12:46:04 GMT

For "last 5 lots" you can use Rank Reverse:

maybe even last 5 lots per "Site" - like in the screenshot?

a bit surprising: the x axis doesn't adjust to the Data Filter settings.
-> a lot of white space

Re: How to filter to show last n items/values?

markschahl — Thu, 24 Jul 2025 10:12:33 GMT

I used the Last.6.Months False/True formula to help create a subset. Used subset instead of filter so the subgroups in the control chart would not include the Last.6.Months == 0 cases.

Names Default To Here( 1 );
dt = Current Data Table();
Last.6.Months.Data = dt << Subset( Filtered Rows(:Running? == 1 & :Last.6.Months == 1));
Last.6.Months.Data << Set Name("Last.6.Months.Data");

Re: How to filter to show last n items/values?

markschahl — Thu, 24 Jul 2025 23:28:29 GMT

Jarmo:
Thanks! I did not know about the Rank Reverse Order function. I will try it out next week.

Yes, the x-axis problem is why I used subset instead of Data Filter.

Re: How to filter to show last n items/values?

hogi — Fri, 25 Jul 2025 07:35:02 GMT

actually , you are right - the necessary function is not yet available.

Let's learn from my mistake:

Col Rank counts based on rows - not based on lots lots.

So every wafer will get its own number. -> not what you want.

Sorry.

But there is a nice outlook:
With version 19, JMP will get the new feature : )

At the moment, there is no one-click option to calculate the Reverse Score Unique.

As a workaround:
There will be another new functionality in JMP19:

This allows us to calculate the Reverse Unique Score via:

(Col N Unique( :lot_id ) - Col N Unique( :lot_id )) + 1

@jthi , nice collaboration : )

Re: How to filter to show last n items/values?

hogi — Fri, 25 Jul 2025 05:41:37 GMT

As an alternative, one can add a Reverse Score Unique manually:

via a custom function

Add Custom Functions(
	New Custom Function(
		"COL",
		"Score Unique_reverse_addExpr",
		Function( {value},
			 Col N Unique( value) - Col Score (value) +1  
		),
		<<Formula Category( "Statistical" ),
		<<Transform Category( 1 ),
		<<Description( "Reverse Score Unique - e.g. for : the last 5 lots" ),
		<<Example( JSL Quote( New Column( "my reverse score", Formula( COL:Score Unique_reverse_addExpr( Expr(:height) ) ) );) )				
	)
);

with the following restrictions:

the grouping options are missing
Expr() has to be added manually in Formula Editor (to protect the argument from being evaluated too early):

https://community.jmp.com/t5/Discussions/Fast-Way-to-calculate-quot-nested-quot-Col-aggregations-like-MAD/m-p/888083/highlight/true#M105027

Re: How to filter to show last n items/values?

jthi — Fri, 25 Jul 2025 14:36:07 GMT

We do have quite nice "select last" and so on with configure levels but too bad, these are not accessible for (nominal/ordinal) data filters

There are plenty of improvements which data filters could benefit from, this could be one more, so maybe a wish list item for this type of selection?

Re: How to filter to show last n items/values?

hogi — Fri, 25 Jul 2025 14:38:24 GMT

Add dense ranking to Ranking Tie and Col Rank functions is not yet marked as "Yes, stay tuned".
With some luck they can squeeze in the "reverse" version of dense ranking for JMP19 in September ; )

Re: How to filter to show last n items/values?

AnnieDudley — Tue, 05 Aug 2025 18:26:00 GMT

Good news! Starting in JMP 19.0, there is a new feature in Control Chart Builder called, "Set Last N Subgroups", which will adjust the X-axis to always show the most recent N subgroups, regardless of any updates to the data or chart.

Re: How to filter to show last n items/values?

markschahl — Thu, 07 Aug 2025 03:53:01 GMT

Thanks for the confirmation, Annie!

I attended your talk at Discovery24, and I thought you mentioned this as an improvement. Looking forward to it.