cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
adam
Level IV

Value ordering by date

Hi All,

 

I have a question on value ordering. Refer to picture attached, how can re-arrange the month/day in ascending order. In this case, the day/month is unpredictable (eg. it can be on Feb03 and the next date is Feb10 follow by Feb 18 and the list is long for a span of 6-12months. I knew that I can manually select and Move Up/Down. How can I do it in a script. 

 

Thank you.

 valueorder2.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
cwillden
Super User (Alumni)

Re: Value ordering by date

You can work out a scripting solution for that, but I would think it would be far easier to just convert this column to have a valid date format so that JMP will do the correct value ordering automatically.

Here's one way to do that that creates a new column "H_Date2"

dt = Current Data Table();
dt << New Column("H_Date2",formula(Num( If(IsMissing(Word(2, :H_Date)),"01",Word(2, :H_Date)) || Word( 1, :H_Date ) || "2018" )));
dt:H_Date2 << Format("ddMonyyyy","ddMonyyyy");
dt:H_Date2 << Modeling Type("nominal");

Basically what it is doing is taking the first and second words in the column H_Date to construct a value of the form "01Jan2018" from "Jan 01".  JMP automatically recognizes character strings like this as a date, so you can just convert the resulting character string to a numeric date-time value using num().

 

I noticed you had a value "Jan" without a day value.  In this case, I just insert "01" as the day.  If that's not how you want to handle that, you can have it do whatever you want.  That's all handled in this bit:

If(IsMissing(Word(2, :H_Date)),"01",Word(2, :H_Date))

 

Next, you need to put it in a readable date format, which is what Format() does.  You can substitute any date format you like for the first argument if you don't want "ddMonyyyy".

 

Finally, I assume you'd want the modeling type to be nominal instead of continuous.  That's what the last line does.


You can verify it does the correct value ordering by adding a value ordering property to the column H_Date2 and looking at the default ordering.

-- Cameron Willden

View solution in original post

2 REPLIES 2
cwillden
Super User (Alumni)

Re: Value ordering by date

You can work out a scripting solution for that, but I would think it would be far easier to just convert this column to have a valid date format so that JMP will do the correct value ordering automatically.

Here's one way to do that that creates a new column "H_Date2"

dt = Current Data Table();
dt << New Column("H_Date2",formula(Num( If(IsMissing(Word(2, :H_Date)),"01",Word(2, :H_Date)) || Word( 1, :H_Date ) || "2018" )));
dt:H_Date2 << Format("ddMonyyyy","ddMonyyyy");
dt:H_Date2 << Modeling Type("nominal");

Basically what it is doing is taking the first and second words in the column H_Date to construct a value of the form "01Jan2018" from "Jan 01".  JMP automatically recognizes character strings like this as a date, so you can just convert the resulting character string to a numeric date-time value using num().

 

I noticed you had a value "Jan" without a day value.  In this case, I just insert "01" as the day.  If that's not how you want to handle that, you can have it do whatever you want.  That's all handled in this bit:

If(IsMissing(Word(2, :H_Date)),"01",Word(2, :H_Date))

 

Next, you need to put it in a readable date format, which is what Format() does.  You can substitute any date format you like for the first argument if you don't want "ddMonyyyy".

 

Finally, I assume you'd want the modeling type to be nominal instead of continuous.  That's what the last line does.


You can verify it does the correct value ordering by adding a value ordering property to the column H_Date2 and looking at the default ordering.

-- Cameron Willden
adam
Level IV

Re: Value ordering by date

Thanks Cameron! That's a good solution.