UPCOMING WEBINAR: The complete guide to analyzing NPS 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.
Please note these steps require a Displayr license.
Method
1. Select your table.
2. Go to Data > RULES on the object inspector.
3. Click the Plus (+) button.
4. Click New custom rule (write your own JavaScript) > Edit JavaScript.
5. 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.
6. OPTIONAL: Change main_stat if you wish to use a different table statistic.
7. OPTIONAL: Change columns_to_modify to include the table column labels you wish to apply the calculation to.
8. OPTIONAL: Change column_for_denominator to the label of the column you wish to use for dividing.
9. Press the blue Play button > OK > OK.