nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Changing of column formats not working as I expect <<Format(Duration, :hr:m:s)

UberBock

So I have a column that has a formula that subtracts 2 dates (two other columns. The output is Numeric Continous. I am trying to use

column(dt,"Duration")<<data type(Numeric)<<Modeling Type(Continuous)<<Format(Duration,:hr:m:s);

to change it to a duration instead of a number. But it doesnt work

column(dt,"Duration")<<data type(Numeric)<<Modeling Type(Continuous)<<Format(Duration,:hr:m:s,17,0);

also doesnt work

I can go into the Colum info menu and change Best to Duration to hr:m:s and that works just fine

Accepted answers

All comments

Jeff_Perkinson

You've got the syntax for the Format() message wrong. It should be:

column(dt,"Duration")<<data type(Numeric)<<Modeling Type(Continuous)<<Format("hr:m:s",17,0);

I figured this out by setting the format interactively in the Column Info dialog, and then I copied the Table Script to see the syntax.

Paste the script into a new script editor and you'll see the section that defines the Duration column:

New Column( "Duration",
		Numeric,
		"Continuous",
		Format( "hr:m:s", 13, 0 ),
		Input Format( "hr:m:s", 0 ),
		Formula( :Column 1 - :Column 2 ),
		Set Selected
	)

UberBock

That does not seem to work.

I created a table with 1 column "test" that had 2 rows with numbers

and then a script for that table with the below

column("test")<<data type(Numeric)<<Modeling Type(Continuous)<<Format(Duration,":hr:m:s",17,0)

and could not change it to Duration

UberBock

ah...I didnt understand the second part....in your post

UberBock

So I cant just change the column format to Duration with <<Format(Duration,":hr:m:s",17,0)

I can change the format of column with <<Format(Best,10,3) in a post-query script

I have a post query script that creates a new column that has a formula which subtracts two other columns. Then try to change that answer with the <<Format(Duration, ":hr:m:s",17.0).

So you suggest that I would have to use...with :Column 1 and :Column 2 those that have the dates that I am subtracting

column(dt,"Duration")<<data type(Numeric)<<Modeling Type(Continuous)<<Format("hr:m:s",17,0),
New Column( "Duration",
Numeric,
"Continuous",
Format( "hr:m:s", 13, 0 ),
Input Format( "hr:m:s", 0 ),
Formula( :Column 1 - :Column 2 ),
Set Selected
);

Jeff_Perkinson

I'm not sure I have enough of your particular details to know for sure how to help. So, let's back up a second to your first message:

@UberBock wrote:
So I have a column that has a formula that subtracts 2 dates (two other columns. The output is Numeric Continous.

Where is this column with the result of the subtraction coming from? I assumed it you were doing this subtraction in JMP, but now I'm not so sure. Is this a computation that has already been done in a column in your database? If so, that may complicate things since JMP doesn't know what units the time value represents.

Dates and times in JMP are stored in seconds, so the :hr:m:s format will show a value of 1 as :0:0:1 and a value of 60 as :0:1:0 and a value of 3600 as :1:0:0. (You can learn more about date time values in Using dates, times, datetimes and durations in JMP.)

So, let's start with that. Where is the column of the difference in the dates being computed, and what units is the result in?

UberBock

Sure.

So I have created an SQL query that I put into 'custom SQL' to get data. It returns a data table that I modifiy with the post-query script. It does a bunch of things. The returned data table has a 'completiondate' column. Its format is like 07/03/2019 12:25:00 AM. In one part of that post-query script I create a summary table that has a min and max from that 'completiondate' column . I was then hoping to create another column that has a formulae which subtracts the min from the max and I wanted the format of that column to be a duration. I tried to create the column and have it use the formulae and then format it as duration but that doesnt seem to work. I have created other columns with formula and changed the format to percent without issue. It seems that JMP dosent like the <<Format(Duration ":hr:m:s:",17,0) the same as say <<Format(Percent, 12,2)