topic Re: Filtering out data from the last recorded day to the week prior. in Discussions

Filtering out data from the last recorded day to the week prior.

TheJVilla — Sat, 10 Jun 2023 23:15:02 GMT

Hello, first project here and first post. I'm very new to JMP and JSL for that matter and have learned a lot with the few days I've had with it.

So, I need grab data from a huge table that would include the most recently recorded date and backwards a week (as the title says). I am using a local data filter and used the Today() function and subtracted In Days(7), which would have worked... only if data were to be updated daily which it is not. So my question would be is if there is a way to be able to use the Local Data Filter to find the most recent date in a column and from that day, go back a week prior without hard coding it (needs to update daily if there is data) like it is done below.

Using the Weekly Weather Data for example:

Tabulate(
       Show Control Panel( 0 ),
       Add Table(
              Column Table( Analysis Columns( :LATITUDE ), Statistics( Mean ) ),
              Row Table( Grouping Columns( :NAME ) )
       ),
       Local Data Filter(
              Add Filter(
                     columns( :DATE ),
                     Where( :DATE >= 19Dec2016 & :DATE <= 26Dec2016 )
              )
       )
);

Because today is June 22, 2020 and the last recorded date was Dec 26, 2016, how would I be able to automatically keep checking everyday forward for new data but if nothing is added be able to go to the most recent data and traverse back a week?

Hopefully what I said makes sense. Thank you for any advice everyone.

Re: Filtering out data from the last recorded day to the week prior.

ih — Mon, 22 Jun 2020 20:02:58 GMT

Take a look at the col max function:

names default to here(1);
dt = open("$Sample_Data\Functional Data\Weekly Weather Data.jmp");
dt << Tabulate(
       Show Control Panel( 0 ),
       Add Table(
              Column Table( Analysis Columns( :LATITUDE ), Statistics( Mean ) ),
              Row Table( Grouping Columns( :NAME ) )
       ),
       Local Data Filter(
              Add Filter(
                     columns( :DATE ),
                     Where( :DATE >= col max(:Date) - in days(7) )
              )
       )
);

Re: Filtering out data from the last recorded day to the week prior.

TheJVilla — Mon, 22 Jun 2020 20:51:11 GMT

I knew it had to be something simple. I made it so much harder than it needed to be. It's working great so far (on a much, much bigger table) so thank you very much! If something comes up I'll say something, but thanks a bunch!