topic How to Select Rows with Date < number of days from Today in Discussions

How to Select Rows with Date < number of days from Today

WebDesignesCrow — Fri, 09 Jun 2023 16:55:51 GMT

Hello,

My data table contain 90 days of data (e.g 03/01/2022 to 03/04/2022).

(The min & max date are not fixed because my source file will be updated daily with latest 90 days from today)

I only need latest 30 days of data in my data table for 1 type of analysis.

(e.g I want to retain rows with date from min=03/03/2022 to max=03/04/2022)

So, I want to select rows with End date > 30 days & delete it.

I used lines as below but it didn't work.

//Remove data with End date  < Today - 30 days (not required for analysis)
Current Data Table() << Row Selection( Select where( :End date < (Today () - in days(30)  ))) << delete rows;

I check the log, it shows that the line is scriptable but I don't see any changes on my data table.

Any advice?

I'm using JMP15

Re: How to Select Rows with Date < number of days from Today

txnelson — Mon, 04 Apr 2022 03:49:28 GMT

There appears to be a bug in JMP 15 with the Row Selection() function. You can get what you want by just using the Select Where()

Current Data Table() <<  Select where( :End date < (Today () - in days(30)  )) << delete rows;

It has been fixed in JMP 16

Re: How to Select Rows with Date < number of days from Today

jthi — Mon, 04 Apr 2022 05:02:44 GMT

In cases like this I prefer using Date Difference (jmp.com) as it manages time / different intervals usually better. It might require some tries to get it working correctly (play around with alignment) but in my opinion it is worth it

Names Default To Here(1);

row_count = 40;
dt = New Table("dates",
	Add Rows(row_count),
	New Column("End date",
		Numeric,
		Continuous,
		Format("Format Pattern", "<YYYY><-><MM><-><DD> <hh24><::><mm><::><ss>", 19, 0),
		Input Format("Format Pattern", "<YYYY><-><MM><-><DD> <hh24><::><mm><::><ss>", 0),
		<< Set Each Value(Today() - In Days(row_count - Row()))
	)
);

dt << New Column("difference", Numeric, Continuous,
	<< Formula(Date Difference(:End date, now, "Day"));
);

Re: How to Select Rows with Date < number of days from Today

WebDesignesCrow — Tue, 05 Apr 2022 08:30:14 GMT

Thanks Jim. Problem solved ! I overthink on writing the line.