475640_en-US

liliconcom

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

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

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

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

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><-><DL><DD> <HH24><::><MM><::><SS>", 19, 0),
  Input Format("Format Pattern", "<YYYY><-><MM><-></MM></YYYY></SS></MM></HH24></DD><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"));
);
<P>&nbsp;</P>
<P>&nbsp;</P>
<P><SPAN class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="jthi_0-1649048527739.png" style="width: 400px;"><span class="lia-inline-image-display-wrapper" image-alt="jthi_0-1649048527739.png" style="width: 400px;"><img src="https://us.v-cdn.net/6038925/uploads/images/41449i2147C238B08C2765/jthi_0-1649048527739.png" role="button" title="jthi_0-1649048527739.png" alt="jthi_0-1649048527739.png" /></span></SPAN></P>
<P>&nbsp;</P></SS></MM></HH24></DD></DL></MM></YYYY>