- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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"));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select Previous Year
Works better than the LearnBot suggestion. Would be nice if the add-in could use replies/answers from the community posts