topic new column & create M/Y date in Discussions

new column & create M/Y date

Thu, 28 Jan 2010 23:26:26 GMT

I'd like to create a new date column based on data in other columns. I have 2 columns containing dates in m/d/y format. I have been able to parse out the month and year for each to create 4 new vars.

Now, I'd like to create a new var that is based on the month and year of one date, and then I'll backfill the second date if the first is missing, but I want the new var to be in M/Y format.

I am not seeing any script guidance as to how to pull the month and year back together to create the new M/Y var. Any assistance is much appreciated!

Re: new column & create M/Y date

Thu, 28 Jan 2010 23:33:38 GMT

dang it, i just figured it out. sorry for posting my query too hastily.

Re: new column & create M/Y date

Fri, 19 Feb 2010 15:52:17 GMT

hello,

could you shortly explain? Have the same problem. Very pedestrian going back to excel, doing the grouping there and then for analysis back to JMP.

Thank you.

Re: new column & create M/Y date

Fri, 19 Feb 2010 16:11:11 GMT

This is the script I ended up using. There is probably a more elegant way of doing the same thing, but this works well enough for my purposes:

New Column( "start.month", Numeric, Continuous, Format( "Best", 10 ), Formula( Month( :start.date ) ) );
New Column( "start.year", Numeric, Continuous, Format( "Best", 10 ), Formula( Year( :start.date ) ) );

New Column("start.date.rev", format("m/y"),formula(Date MDY( :start.month, 1, :start.year)));

for each row(
If(IsMissing(admin.date),admin.date=start.date.rev);
If(IsMissing(admin.year),admin.year=start.year);
);

Re: new column & create M/Y date

rossmiller — Fri, 19 Feb 2010 22:28:35 GMT

i've been trying to do exactly the same thing, change a full date into m/y format. is there a way to do this without running a script using a formula?

i created the columns using the month and year functions, but i can't concat them together

Re: new column & create M/Y date

ms — Fri, 19 Feb 2010 22:45:51 GMT

This formula seem to work,

Informat( Char( Month( :Date ) ) || "-" || Char( Year( :Date ) ), "m-y" )

Remember to set the column format to numeric, continuous, and date: m-y.

new column & create M/Y date

GoodMan — Wed, 30 Nov 2011 12:39:42 GMT

MS, thanks for your answer to this data question. If i need output format like"y-m", not "m-y", how can i realize with formula? Here in my country, we just accept "y-m" or "y-m-d". Thank you.