How to filter to show last n items/values?

[#1]

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.

[#2]

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.

[#3]

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

[#4]

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

[#5]

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.

[#6]

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 : )
Add dense ranking to Ranking Tie and Col Rank functions 

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: Col N Categories (💚) 

This allows us to calculate the  Reverse Unique Score via:

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

@jthi , nice collaboration : )

[#7]

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-lik... 

[#8]

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?

[#9]

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

[#10]

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