Introduction
This article describes how to use a Custom Rule to relabel date labels on a table for a date/time variable in Q.
Requirements
A date/time variable used in a table.
Method - Abbreviating Month Names Automatically
This example may be used to abbreviate month labels for a Date question when aggregated to monthly. Instead of showing the full month and year by default, e.g. 'January 2019', it will truncate the label to 3 letters, i.e. 'Jan'. To relabel dates on a table to the abbreviated month through a rule in the menu, see How to Abbreviate Month Labels in Tables.
To use this snippet:
- Select your table.
- Select Automate > Custom Rule.
- Paste in the code from below.
- Click the 'Play' icon and close.
var row_labels = table.rowLabels;
var col_labels = table.columnLabels;
var months = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
// Check row labels and update
var split_labels = row_labels.map(function (str) { return str.split(" ")[0]; });
if (split_labels.every( function (str) { return months.indexOf(str) > -1; })) {
split_labels = split_labels.map(function (str) { return str.substring(0, 3); });
table.rowLabels = split_labels;
}
// Check column labels and update
var split_labels = col_labels.map(function (str) { return str.split(" ")[0]; });
if (split_labels.every( function (str) { return months.indexOf(str) > -1; })) {
split_labels = split_labels.map(function (str) { return str.substring(0, 3); });
table.columnLabels = split_labels;
}
Method - Specifying the Month(s) in Date Labels
This example may be used to change labels for a Date question that use months in the label. This can be a date/time variable that's aggregated to Monthly, Quarterly, or a range of months. It will leave the year portion and replace the month portion of the date label with whatever you specify.
To use this snippet:
- Select your table.
- Select Automate > Custom Rule.
- Paste in the code from below.
- Click the 'Play' icon and close.
// Abbreviate Month Names
//setup Rule name
form.setHeading("Specify Dates Labels");
let description = form.newLabel("Rename date labels");
description.lineBreakAfter = true;
//Create UI for the user to select if dates are in the rows or columns
let label = form.newLabel("Dates in");
let row_or_column_box = form.newComboBox("rowcol", ["columns", "rows"]);
row_or_column_box.setDefault("columns");
form.setInputControls([description, label, row_or_column_box]);
let do_rows = row_or_column_box.getValue() == "rows";
form.setSummary("Abbreviate dates names in " + row_or_column_box.getValue());
//create 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) {
//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 month part of the label
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
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 for 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;
else return month_prefix + " '" + year;
});
//set either the row or column labels to the new labels
if (do_rows)
table.rowLabels = labels;
else
table.columnLabels = labels;
Method - Relabeling dates with forward slashes /
To use this snippet:
- Select your table.
- Select Automate > Custom Rule.
- Paste in the code below and modify as appropriate according to the comments (prefixed by //)
- Click the 'Play' icon and close.
//////////
//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;
See Also
How to Create Date Variables in Q
How to Set Time Periods for Date Questions