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 variable with a Structure of Date/Time in the columns of your table. This is so that the periods are based on a timeline and the previous period can be identified for testing. There are various ways of converting a variable into a Date/Time variable that you can review in How to Create Date Variables in Displayr. However, sometimes you may have irregularly sized time periods (i.e. 2021 vs Q1 2022) or want to still show "Wave 1, Wave 2, etc" as column headers in your table. 

This article explains how to work around this limitation by first assigning fake dates to the different periods in a new Date/Time variable to get the correct stat testing, and then applying a custom rule to relabel those fake dates to the appropriate time period they represent. This will ultimately 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 variable with mutually exclusive time periods, i.e. with a Structure of Nominal or Numeric, 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:
  
• Note that if you are working with overlapping time periods, you either will need to:
  • Format them into a Binary-Multi question, and you will only be able to compare to the previous time period using custom column comparisons.
  • Stack your data so that each row of data is allocated to only one of the time periods. Then you can follow the method below and use the compare to previous period with the arrows and font colors.

Method

1. Insert a new JavaScript variable to represent the fake Date/Time variable to use for the stat testing. The code will assign each period of your categorical date/wave variable to an individual year in sequential order based on the Value in the Value Attributes (see above). The years will begin in the 4700s so it is very obvious, if shown on a table, that there is a labeling issue you need to address, and to be sure that the rule used to rename these columns will not accidently rename columns with actual yearly dates in them. 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 Data 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 (VERY far in the future) that you created and not the period labels (which come later):
   
4. Now we will rename these yearly column headers to the periods they actually represent. Select the table and add a custom rule via Data > Rules > + > New custom rule (write your own JavaScript) using the below code. The name of the new rule will be Correct wave names.

       // 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);
   }

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. Optional: You may wish to apply this to all tables and charts and any new tables and charts created. This will ensure that any table that uses the fakedate will be relabeled. To do so, 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 to a different new number rounded to the closest 0. You will need to use this new number instead of 4700 in the JavaScript variable code in step 1 above, and use the new number + 1 instead of 4701 in the code from step 4.