How to Create an Index

Introduction

This article describes how to create an index in Q. An index, where a value of one Statistic is divided by another, can be computed either automatically as a Statistic, by Table JavaScript and Plot JavaScript, or by creating new variables.

Method

The Index statistic

On tables involving percentages, select Statistics - Cells > Index.

Table Javascript or Plot Javascript

As an example, the following JavaScript modifies the values of the Average in each cell by dividing by the Average from a column labeled either 'NET' or 'Total', and multiplying by 100. To apply this, create a Custom Rule.

// Checking that the table is suitable.
if (table.statistics.indexOf('Average') == -1)
  form.ruleNotApplicable("this rule requires the table to have 'Average' in the cells.");
 
// Finding the NET or Total column.
var column_labels = table.columnLabels;
var base = column_labels.indexOf("NET");
if (base == -1)
    base = column_labels.indexOf("Total");
if (base == -1)
  form.ruleNotApplicable("this rule requires the table to have a column called 'NET' or 'Total'.");

// Computing the indexes and writing them over the top of the existing averages.
var averages = table.get('Average'); // Extract the averages.
for (var row = 0; row < table.numberRows; row++) {
    var net = averages[row][base];
    for (var column = 0; column < table.numberColumns; column++) 
        averages[row][column] = averages[row][column] / net * 100;
}
// Writing the result back to the table.
table.set('Average', averages)

Note that if using this method, the index is automatically rebased whenever you change the weight and filter.

Note also that the statistical testing results shown on the table relate to the original values of the Average, and this can lead to counter-intuitive highlighting when the data contains negative values.

The code below does the same thing, except indexing within columns rather than rows:

// Checking that the table is suitable.
if (table.statistics.indexOf('Average') == -1)
  form.ruleNotApplicable("this rule requires the table to have 'Average' in the cells.");
 
// Finding the NET or Total row.
var row_labels = table.rowLabels;
var base = row_labels.indexOf("NET");
if (base == -1)
    base = row_labels.indexOf("Total");
if (base == -1)
  form.ruleNotApplicable("this rule requires the table to have a row called 'NET' or 'Total'.");
 
// Computing the indexes and writing them over the top of the existing averages.
var averages = table.get('Average'); // Extract the averages.
for (var column = 0; column < table.numberColumns; column++) 
{
    var net = averages[base][column];
    for (var row = 0; row < table.numberRows; row++) 
        averages[row][column] = averages[row][column] / net * 100;
}
// Writing the result back to the table.
table.set('Average', averages)

Creating new variables if computing an index from the Average statistic

1. Go to the Variables and Questions tab.
2. Right-click on the variable that you wish to compute an index from, and select Copy and Paste Variable(s) > Linked.
3. Change the Question Type to Number (it may already be of this type).
4. Right-click on the newly credit variable and select Edit variable.
5. Select Access all data rows (advanced).
6. Paste in the Expression below
7. Modify the Expression by replacing myVar with the name of the variable that you originally made the linked copy of (this will be the variable name that appeared in the Expression prior to your pasting the contents from below).
8. If you are analyzing the data with a weight, replace the null in the second line with the name of the weight variable.
9. If you are analyzing the data with a filter, replace the null in the third line with the name of the filter variable.
10. Press OK.
11. Select the variable in the Blue Drop-down menu on your table.

Note that if using this method, you need to manually modify the Expression whenever you change the weight and filter.

Note also that if the question you wish to cross-tabulate with contains missing values then you should incorporate this into the formula as a filter using the following:

1. Create a SUMMARY table with the question selecting in the Blue Drop-down menu.
2. Right-click on the statistic in the NET row and select Create Filter.
3. Find the new filter variable in the Variables and Questions tab and take note of the name.
4. Right-click the variable create above and select Edit Variable.
5. Replace the null in the third line the the variable name of the new filter. If you have already incorporated a filter then you can include both names with a plus-sign, + between them (e.g. var my_filter = filter1 + filter2;).

To repeat the formula for several variables you can use Template for Replication.

var my_variable = myVar;
var my_weight = null;
var my_filter = null; 

// Computing the average.
var base = 0;
var sum = 0;
for (var i = 0; i < N; i++) {
    if (my_filter == null || my_filter[i] > 0) {
        var w = my_weight == null ? 1 : my_weight[i];
        var v = my_variable[i];
        if (!isNaN(v) & !isNaN(w) & w > 0) {
            base += w;
            sum += v * w;
        }
    }
}
var average = sum / base;

// Rebasing the average as an index.
for (var i = 0; i < N; i++) 
    my_variable[i] *= 100 / average;

my_variable;

Next

How to Create New Variables

How to Use JavaScript in Q

JavaScript Fundamentals