cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
Choose Language Hide Translation Bar
benc
Level I

Select Previous Year

I'm in the process of automating some monthly reports, and am having trouble selecting the data I need. Suppose there is a column of dates. I would like a JSL script that selects the previous 12 months of data, based on the current month.
Example: We are in August 2021. I would like the script to select all dates from August 2020 to present. Next month when it is September I would like it to select September 2020 to present without any input from me.
How can I do this? Some other applications like Access and PowerBI have a "Select Previous Year" feature in their interface, but I can't find anything similar in JMP.

2 REPLIES 2
txnelson
Super User

Re: Select Previous Year

One way to do this is

dt = current data table();
dt << select where(:date >= DateMDY(month(today()),1,year(today())-1);
dtSub = dt << subset(selected rows(1), selected columns(0));
Jim
jthi
Super User

Re: Select Previous Year

I strongly suggest using Date Increment function with tasks like this. Below is an example with two different dates:

Names Default To Here(1);
date1 = Date DMY(15,8,2021);
date2 = Date DMY(1,9,2021);
Show(Date Increment(date1, "month", -12, "start")); //Date Increment(date1, "month", -12, "start") = 01Aug2020;
Show(Date Increment(date2, "month", -12, "start")); //Date Increment(date2, "month", -12, "start") = 01Sep2020;
Show(Date Increment(Today(), "month", -12, "start")); 

Here is a version to be used with data table with :date column:

Names Default To Here(1);
dt = Current Data Table();
dt << Select Where(:date >= Date Increment(Today(), "month", -12, "start"));
-Jarmo