Changing how the Index is Calculated

From Q
Jump to navigation Jump to search

This example shows how to change how the Index statistic is calculated. It allows you to calculate the Index by dividing by a particular column in the table. If you need to, you can apply this several times to change how the index is computed in a set of different columns.

To use this code in your own project:

  1. Select Automate > Custom Rule.
  2. Paste in the code.
  3. Change columns_to_modify and column_for_denominator to match the columns you want to change, and the column to use as the denominator.
  4. Click the play icon, and then Close.
includeWeb("JavaScript Array Functions")

var index_stat = "Index";
var main_stat = "Column %";

var columns_to_modify = ["Coca-Cola", "Diet Coke", "Coke Zero", "Pepsi", "Diet Pepsi", "Pepsi Max", "Dislike all cola", "Don't care"];
var column_for_denominator = "Coca-Cola";
form.setSummary("Index " + columns_to_modify.join(", ") + " to " + column_for_denominator);

if (table.availableStatistics.indexOf(main_stat) == -1)
    form.ruleNotApplicable(main_stat + " is not available on this table");
if (table.availableStatistics.indexOf(index_stat) == -1)
    form.ruleNotApplicable(index_stat + " is not available on this table");
if (table.columnLabels.length != uniqueElementsInArray(table.columnLabels).length)
    form.ruleNotApplicable("the column labels contain duplicates");

var target_columns = (col) { return table.columnLabels.indexOf(col); });
if (target_columns.some(function (x) { return x == -1; }) ) {
    form.ruleNotApplicable(columns_to_modify[target_columns.indexOf(-1)] + " not found in this table");
var denominator_column = table.columnLabels.indexOf(column_for_denominator)
if (denominator_column == -1)
    form.ruleNotApplicable("there is no column called " + column_for_denominator);

var stats = table.get(main_stat);
var indexes = table.get(index_stat);

target_columns.forEach(function (target_column) {
    for (var row = 0; row < table.numberRows; row++) {
        indexes[row][target_column] = stats[row][target_column] / stats[row][denominator_column] * 100;

table.set(index_stat, indexes);

Note that:

  • The Indexes are calculated by dividing the Column % in one column by the Column % in the denominator column and multiplying by 100.
  • This example will only work on tables that contain Column % and Index.
  • This example will not work if there are duplicate column labels in the table.

See also