How to Relabel a Date/Time Question in a Table in Q

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:

1. Select your table.
2. Select Automate > Custom Rule.
3. Paste in the code from below.
4. 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:

1. Select your table.
2. Select Automate > Custom Rule.
3. Paste in the code from below.
4. 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;

See Also

How to Use Rules in Q

How to Create Date Variables in Q

How to Set Time Periods for Date Questions

How to Work with Date Questions With Irregular Time Periods

How to Relabel Rows and Columns in an R Table