Articles in this section

See more

How to Work with Date Questions With Irregular Time Periods

  • Updated
Follow

Sometimes, you may want to compare, different sized periods, such as: 2021, Q1 2022, and April 2022. If you want to compare each against the other, you can simply create a Pick One or Pick Any (if you have overlapping time periods) question to do this, but if you're wanting to run statistical tests against the previous period a Date/Time question is required. However, Date/Time questions assume dates are one frequency (monthly, daily, quarterly, etc). This article shows you how to work around this limitation by having varying time periods while keeping the question as a Date/Time variable.

 

Requirements

  • Mutually exclusive irregular time periods.
    • Note that if you are working with overlapping time periods, you either will need to:
      • Format them into a Pick Any 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 rolling time periods. Then you can use the compare to previous period with the arrows and font colors.

Method

1. Create a variable that 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 this example, we will assume the variable name of this variable is timePeriods. If such a variable is not in your data file, the fastest way to create it will usually be as a JavaScript variable. An easy way to do this is to:

  • Create filters for each time period.
  • In the Variables and Questions tab, if required, change the names of the variables to period1, period2, etc.
  • Insert a JavaScript variable via right-click > Insert Variable(s) > JavaScript Formula > Numeric with an Expression of period1 + 2 * period2 + 3 * period3 etc..
  • Change the Name and Label of the variable to timePeriods.
  •  

2. Insert a new JavaScript variable with:

  • Name: fakeDate
  • Label: Whatever you wish to appear in the report. E.g., Wave.
  • Expression: Q.EncodeDate(timePeriods+4700, 1, 1)
  •  

3. Set the Variable Type to Date/Time.

4. Press the Values button and check that the Aggregation is set to 1 and Year.

5. Make a table with this variable in the Blue or Brown drop-down.

6. Select Automate > Custom Rule and paste 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);
}

7. Modify the contents of the first line 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).

8. Press Close and OK.

9. Move the newly-created Custom Rule called Correct wave names to the top of the Rules list. 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.

10. To the right of the rule, select Apply > Add to every item in the project.

11. To the right of the rule, ensure that New Items is checked.

 

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 from step 2 and 4701 in step 5 above.

 

Next

How to Use Rules in Q

How To Test Between Adjacent Time Periods

 

Related articles