452081_en-US

liliconcom

How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

In the past I have substracted Month only string (JAN) from Jan-14 and then recoded individual months to new label (JAN) = (Q1). However this tack is very manual, is there a formula i can embed to automate this in the future?

ScriptingRe: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

Thanks Jarmo - the local variable feature is new to me but looks like it could be a game changer if i get the hang of it!

Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

Create new column and then add this as formula:

Local({month_list = (month_list = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"})},
   Ceiling(Contains(month_list, Word(1, :MonthYear, "-")) / 3)
  )

Make sure you have correct column name in :MonthYear (in image below it is A)

 

Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

 

Attempted to apply but perhaps i have input formula with basic error?

Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

Appreciate the quick response - I'm on JMP 14 so do not have format pattern. I have added a sample file, could you perhaps show me how to use conversion list?

 

Thanks!

Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

If you have JMP16 you could first convert the character column to date column with format pattern and then use Quarter function

Names Default To Here(1);

dt = Current Data Table();
Column(dt, 1) << Set Name("MonthYear");
Column(dt, 1) << Data Type(Numeric, 
 Format("Format Pattern", "<MMM><-><YY>", 35),
 Input Format("Format Pattern", "<MMM><-><YY>")
);

dt << New Column("Q", Numeric, Ordinal, Formula(Quarter(:MonthYear)));</YY></MMM></YY></MMM>

If you don't have JMP16 you could do "conversion list" of months and then use that instead to get Quarters

 

Edit:

Formula for JMPs before 16 and no conversion to first column using list of months, contains(), word() and ceiling()

dt << New Column("Q",
 Numeric,
 Ordinal,
 Formula(
  Local({month_list = (month_list = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"})},
   Ceiling(Contains(month_list, Word(1, :MonthYear, "-")) / 3)
  )
 )
);