Date/Time variables are labeled automatically based on how you configure a Date/Time variable. However, sometimes you may want to format these labels, especially if your report is not in English or you have non-standard time periods. You can't do this on the variable itself, but rather will need to apply a rule to built-in tables that show your Date/Time variable. If you're looking to abbreviate long month names to shorter month names you can use an automation, see How to Abbreviate Month Labels in Tables. Otherwise, you can create a more custom abbreviation format or labeling by modifying one of the examples below.
Method - Relabeling dates with spaces
Method - Relabeling dates with forward slashes /
Method - Relabeling any date to anything
Requirements
- A table with the a Date/Time variable in the Rows or Columns.
- Knowledge of How to Configure a Date/Time Variable.
Method - Relabeling dates with spaces
You can relabel a table with dates that have spaces like below:
- With your table selected, click on Properties > RULES > + > New Custom Rule (Write your own JavaScript).
- Click Edit JavaScript.
- You can delete the code currently there - it's all comments. Paste in the code below and modify as appropriate according to the comments (prefixed by //):
//////////
//Change date labels
//////////
////Create UI for the user to select if dates are in the rows or columns
//////////
//give it a heading
form.setHeading("Specify Dates Labels");
let description = form.newLabel("Rename date labels");
description.lineBreakAfter = true;
//create the prompt for where the dates are
let label = form.newLabel("Dates in");
//create the dropdown for the options columns rows
let row_or_column_box = form.newComboBox("rowcol", ["columns", "rows"]);
//set the default to columns
row_or_column_box.setDefault("columns");
//create the final UI putting together the elements above
form.setInputControls([description, label, row_or_column_box]);
//create variable that's TRUE if the user selected rows in the dropdown
let do_rows = row_or_column_box.getValue() == "rows";
//setup Rule name shown in the list to identify it in document
//CHANGE the name below inside the "" to something more specific
//if using this on more than one date variable
form.setSummary("Abbreviate dates names in " + row_or_column_box.getValue());
//////////
//// Create loop with logic to relabel labels
//////////
//create list of labels to loop through based on if we're going to loop through the rows or columns
let labels = do_rows ? table.rowLabels : table.columnLabels;
//setup a function to swap out the labels with the new ones
labels = labels.map(function (label) {
//below is based on dates containing spaces like January 2019 and Jan-Mar 2019
//split each label into two parts at the space first part=month second=year
let label_elements = label.split(" ");
//create new variable month_prefix for the first part of the label before the year
let month_prefix = label_elements[0];
//check the month part of the label for a particular month/text
//if found, change the month part to the relabeled part inside the if statement
//CHANGE the line after the if and else if statements to the date label you'd like use
if (month_prefix.indexOf("January") == 0)
month_prefix = "Jan.";
else if (month_prefix.indexOf("February") == 0)
month_prefix = "Feb.";
else if (month_prefix.indexOf("March") == 0)
month_prefix = "Mar.";
else if (month_prefix.indexOf("April") == 0)
month_prefix = "Apr.";
else if (month_prefix.indexOf("May") == 0)
month_prefix = "May.";
else if (month_prefix.indexOf("June") == 0)
month_prefix = "Jun.";
else if (month_prefix.indexOf("July") == 0)
month_prefix = "Jul.";
else if (month_prefix.indexOf("August") == 0)
month_prefix = "Aug.";
else if (month_prefix.indexOf("September") == 0)
month_prefix = "Sep.";
else if (month_prefix.indexOf("October") == 0)
month_prefix = "Oct.";
else if (month_prefix.indexOf("November") == 0)
month_prefix = "Nov.";
else if (month_prefix.indexOf("December") == 0)
month_prefix = "Dec.";
//QUARTERLY months relabeling
else if (month_prefix.indexOf("Jan-Mar") == 0)
month_prefix = "Q1";
else if (month_prefix.indexOf("Apr-Jun") == 0)
month_prefix = "Q2";
else if (month_prefix.indexOf("Jul-Sep") == 0)
month_prefix = "Q3";
else if (month_prefix.indexOf("Oct-Dec") == 0)
month_prefix = "Q4";
//create variable for second part of label split by space the year
let year = label_elements[1];
//if there isn't two parts to the label the dates are all in one year so leave year off
//otherwise return the new label with the year
if (label_elements.length != 2)
return month_prefix;
//CHANGE " " below if you want to separate the prefix and year by something other than a space
else return month_prefix + " " + year;
});
//////////
//// With relabels created above, now add them to the table
//////////
//if use selected rows set rowLabels otherwise set columnLabels to the new labels
if (do_rows)
table.rowLabels = labels;
else
table.columnLabels = labels; - Click OK -- note that if the wrong columns/rows is in the dropdown you may receive an error, but this will go away once the correct columns/rows option is selected.
Method - Relabeling dates with forward slashes /
- With your table selected, click on Properties > RULES > + > New Custom Rule (Write your own JavaScript).
- Click Edit JavaScript.
- You can delete the code currently there - it's all comments. Paste in the code below and modify as appropriate according to the comments (prefixed by //):
//////////
//Change date labels
//////////
////Create UI for the user to select if dates are in the rows or columns
//////////
//give it a heading
form.setHeading("Specify Dates Labels");
let description = form.newLabel("Rename date labels");
description.lineBreakAfter = true;
//create the prompt for where the dates are
let label = form.newLabel("Dates in");
//create the dropdown for the options columns rows
let row_or_column_box = form.newComboBox("rowcol", ["columns", "rows"]);
//set the default to columns
row_or_column_box.setDefault("columns");
//create the final UI putting together the elements above
form.setInputControls([description, label, row_or_column_box]);
//create variable that's TRUE if the user selected rows in the dropdown
let do_rows = row_or_column_box.getValue() == "rows";
//setup Rule name shown in the list to identify it in document
//CHANGE the name below inside the "" to something more specific
//if using this on more than one date variable
form.setSummary("Abbreviate dates names in " + row_or_column_box.getValue());
//////////
//// Create loop with logic to relabel labels
//////////
//create list of labels to loop through based on if we're going to loop through the rows or columns
let labels = do_rows ? table.rowLabels : table.columnLabels;
//setup a function to swap out the labels with the new ones
//below is based on labels showing a range of dates similar to 1/1/2022-2/1/2022
//the below function takes the second date from the date range
//using the month of that date, it then relabels the header
labels = labels.map(function (label) {
//split each label into the two date parts at the hyphen
let thedates = label.split("-");
//use the second/ending date
let second_date = thedates[1];
//split each label into various date components using /
let label_elements = second_date.split("/");
//check to see that the parsing is working - if doesnt return 3 pieces of the date use the current label
if(label_elements.length < 3) return label;
//create new variable that is the month_prefix for the month part of the label
//CHANGE if you're working with US dates use [0] since month is before the first / for DD/MM/YYYY use [1] below
let month_prefix = label_elements[0];
//check the month part of the label for a particular month/text
//if found, change the month part to the relabeled part inside the if statement
//CHANGE the line after the if and else if statements to the date label you'd like use
if (month_prefix.indexOf("10") == 0)
month_prefix = "Oct.";
else if (month_prefix.indexOf("11") == 0)
month_prefix = "Nov.";
else if (month_prefix.indexOf("12") == 0)
month_prefix = "Dec.";
else if (month_prefix.indexOf("1") == 0)
month_prefix = "Jan.";
else if (month_prefix.indexOf("2") == 0)
month_prefix = "Feb.";
else if (month_prefix.indexOf("3") == 0)
month_prefix = "Mar.";
else if (month_prefix.indexOf("4") == 0)
month_prefix = "Apr.";
else if (month_prefix.indexOf("5") == 0)
month_prefix = "May.";
else if (month_prefix.indexOf("6") == 0)
month_prefix = "Jun.";
else if (month_prefix.indexOf("7") == 0)
month_prefix = "Jul.";
else if (month_prefix.indexOf("8") == 0)
month_prefix = "Aug.";
else if (month_prefix.indexOf("9") == 0)
month_prefix = "Sep.";
//create variable for second part of label split by space the year
let year = label_elements[2];
//CHANGE " " below if you want to separate the prefix and year by something other than a space
return month_prefix + " " + year;
});
//////////
//// With relabels created above, now add them to the table
//////////
//if use selected rows set rowLabels otherwise set columnLabels to the new labels
if (do_rows)
table.rowLabels = labels;
else
table.columnLabels = labels; - Click OK -- note that if the wrong columns/rows is in the dropdown you may receive an error, but this will go away once the correct columns/rows option is selected.
Method - Relabeling any date to anything
You can modify labels in even more custom ways by adding your own custom code into the general template:
//////////
//Change date labels
//////////
////Create UI for the user to select if dates are in the rows or columns
//////////
//give it a heading
form.setHeading("Specify Dates Labels");
let description = form.newLabel("Rename date labels");
description.lineBreakAfter = true;
//create the prompt for where the dates are
let label = form.newLabel("Dates in");
//create the dropdown for the options columns rows
let row_or_column_box = form.newComboBox("rowcol", ["columns", "rows"]);
//set the default to columns
row_or_column_box.setDefault("columns");
//create the final UI putting together the elements above
form.setInputControls([description, label, row_or_column_box]);
//create variable that's TRUE if the user selected rows in the dropdown
let do_rows = row_or_column_box.getValue() == "rows";
//setup Rule name shown in the list to identify it in document
//CHANGE the name below inside the "" to something more specific
//if using this on more than one date variable
form.setSummary("Abbreviate dates names in " + row_or_column_box.getValue());
//////////
//// Create loop with logic to relabel labels
//////////
//create list of labels to loop through based on if we're going to loop through the rows or columns
let labels = do_rows ? table.rowLabels : table.columnLabels;
//setup a function to swap out the labels with the new ones
labels = labels.map(function (label) {
////WRITE CODE TO PARSE THE CURRENT DATE LABEL
////WRITE IF/ELSE CODE TO USE CURRENT LABEL TO RELABEL AS NEEDED
////WRITE CODE TO RETURN LABEL IN CUSTOM FORMAT
});
//////////
//// With relabels created above, now add them to the table
//////////
//if use selected rows set rowLabels otherwise set columnLabels to the new labels
if (do_rows)
table.rowLabels = labels;
else
table.columnLabels = labels;
Next
How to Conduct Significance Tests by Comparing to Previous Time Periods
How to Insert a Date Control and How to Work with Date Ranges Using R
How to Test Against the Previous Period without a Date/Time Variable
Comments
0 comments
Please sign in to leave a comment.