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 missing time periods, or want to have non-date labels "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
At a high-level, the solution here is to assign each time period you want to test, in order, to a fake year in a Date/Time variable. Each "year" can be tested against the previous "year" so the periods will be tested to the previous period -- regardless of the actual timeframe of the data. Then in your tables, you will use a rule to overwrite the fake year headers with the original time periods that correspond to the data.
- Insert a new Date/Time JavaScript variable to be the fake year variable to use to be able to stat test to the previous period. In the Data Sources pane, hover and click Plus (+) > Custom Code > JavaScript > Date/Time.
-
In the Code Editor that pops up at the top, paste in the following code and change timePeriods to the Name of your variable with the time periods.
Q.EncodeDate(timePeriods + 4700, 1, 1)
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 to test, in order, a consecutive year, this enables the stat testing engine to know what period comes before another for testing. Please use the following settings: - In the object inspector, on the General tab change:
- Name: fakeDate
- Label: Whatever you wish to appear in the report, e.g., Wave.
- On the Data tab, press the Date/Time button and change Aggregation > 1 Year. When you use this variable in a table, you will see the yearly values that you created and not the period labels (which come later):
- 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); } - 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"];
- 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:
- 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:
- Select Apply > Add to every item in the document.
- 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.