Introduction
This article describes how to change the default Index calculation...
...to divide by a particular column such as Coca-Cola below:
Requirements
- A table with Column % and Index.
- A table without duplicate column labels.
- The default Index is calculated based on the ratio of the row (or column) percentage to the total for the column (or row) multiplied by 100. In this example, we will instead specify the denominator used in this calculation.
Method
1. Select your table.
2. Go to Automate > Custom Rule.
3. Paste the below into the dialog:
includeWeb("JavaScript Array Functions")
form.setHeading("Custom indexing");
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 = columns_to_modify.map(function (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);
- This code first performs a series of validations to check whether the correct statistics are available on the table and that the specified column labels exist.
- It then loops through each target column and replaces the existing Index value with the result of the new calculation.
4. Change columns_to_modify to include the table column labels you wish to apply the calculation to.
5. Change column_for_denominator to the label of the column you wish to use for dividing.
6. OPTIONAL: Change main_stat if you wish to use a different table statistic.
7. Press the blue Play button > Close > OK.