How to Test Against the Previous Period without a Date/Time Variable

In order to compare one period to a previous period (or one column to the previous column), you must use a Date/Time variable in the columns of your table. However, sometimes people have irregularly sized time periods (2021 vs Q1 2022) or want to show "wave 1, wave 2, etc", which can't be created by a Date/Time variable. This article explains how to work around this limitation by creating a fake Date/Time variable to use in tables to do the previous period stat testing, and then relabeling those time periods to the custom time periods they represent. This will enable to change stat testing from comparing all periods:

to stat testing comparing to the previous period:

Note, if you do not need to do stat testing on the previous period, and you just want to compare, different sized periods, such as: 2021, Q1 2022, and April 2022. You can simply create a Nominal or Binary-Multi variable (if you have overlapping time periods) variable set to do this, see How to Create a Categorical Variable from a Date/Time Variable.

Requirements

• A Nominal or Numeric variable whose Value Attributes contains a 1 for the first time period, a 2 for the second time period, a 3 for the third time period, and so on. In the rest of this article, it is assumed that the variable name of this variable is timePeriods. In our example the variable is coded as:
  

Method

1. Insert a new JavaScript variable to represent the fake Date/Time variable to use with the stat testing. The code will assign each period of your categorical date/wave variable to an individual year in sequential order. By assigning each period a year, this enables the stat testing engine to know what period comes before another for testing. Please use the following settings:
   • Name: fakeDate
   • Label: Whatever you wish to appear in the report, e.g., Wave.
   • Expression: Q.EncodeDate(timePeriods+4700, 1, 1)
     
2. Set the variable's Structure to Date/Time.
3. Press the Date/Time button in the Properties tab of the object inspector and check that the Aggregation is set to 1 and Year. When you use this variable in a table, you will see the yearly values (in the future) that you created and not the period labels (which comes later):
   
4. Now we will rename these yearly column headers to the periods they actually represent. Select the table and add a custom rule using the code below.
5. Modify the contents of the correct_names line of code to list the names that you wish to give to the waves. Note that you will get an error in the next step if you make a mistake (e.g., too many or too few new names, forgetting a comma, or the quotation marks). Our example uses the following:
   

   correct_names = ["2019 Q1","2019 Q2", "July 2019","August 2019","September 2019"];

6. Move the newly-created custom rule called Correct wave names to the top of the list of rules. It is not always required to be at the top of the list, but if you have any other rules that change the names of columns or row labels, it will prevent this rule from being applied, so it is preferable to have this rule at the top. Now you will see the table shows the correct period names:
   
7. Right-click on the rule and:
   1. Select Apply > Add to every item in the document.
   2. Check Use for new tables and charts.

A side-effect of this rule is that any tables containing labels of 4701 or above may inadvertently be renamed. You can work around this by replacing 4700 in the code above, and 4701 correspondingly in the code below.

// Add your correct labels here. 
//You must have as many new labels as categories you want to replace.
correct_names = ["My first wave","2nd wave", "3rd wave"];
form.setSummary("Correct wave names");

// Function to adjust and set labels 
correctNames = function(by_rows, is_span) {
    var labels;
    if (is_span) {
        var spans = by_rows ? table.rowSpans : table.columnSpans;
        if (spans.length == 0)
            return;
        labels = spans.map(function (obj) { return obj.label; } )
    } else
        labels = by_rows ? table.rowLabels : table.columnLabels;
 
    if (labels == null)
        return;
    var n = labels.length;
    if (correct_names.length > n || n > 200)
        return;
    if (labels.indexOf("4701") == -1)
        return;
    for (var i = 0; i < n; i++) {
        var current_label = labels[i];
        if (current_label >=4701)
            labels[i] = correct_names[current_label - 4701];
    }
    if (is_span) {
        // Remove all spans (these will be rebuilt below)
        if (by_rows)
            table.clearRowSpans();
        else
            table.clearColumnSpans();
 
        // Set spans with altered labels
        spans.forEach(function (obj, ind) {
            if (by_rows)
                table.spanRows(obj.indices, labels[ind]);
            else
                table.spanColumns(obj.indices, labels[ind]);    
        })
    } else {
        if (by_rows)
            table.rowLabels = labels;
        else
            table.columnLabels = labels;
    }
}
 
// Adjust labels (and spans in Q 4.11 and higher) 
correctNames(true, false);
correctNames(false, false);
if (fileFormatVersion() >= 8.81) {
    correctNames(true, true);
    if (table.columnLabels != null)
        correctNames(false, true);
}