Value ordering by date

[#1]

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.

 

[#2 · Accepted solution]

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.

[#3]

Thanks Cameron! That's a good solution.