Articles in this section

See more

How to Create a Custom Statistic or Calculation via a Rule

  • Updated
Follow

This article describes how to create a calculation on a table using a Custom Rule. There are two methods depending on what you want to calculate:

 

Requirements

 

Method 1 - Update existing statistic values

Get the statistic values

In the below table, we have a summary grid table that displays the number of colas consumed in different locations as an average.

mceclip0.pngAs a simple example, we will multiply each table cell value by 10.

The first step is to get the cell statistic values using the get function:

var values = table.get("Average");

Here, the average is returned as a two-dimensional array as each cell has its own row and column index.
If we wanted to return STATISTICS > Right or STATISTICS > Below values we would do the following:

var values = right_table.get("Average");
var values = below_table.get("Average");

Loop through each row/column

The standard way to extract and update statistic values is via looping. In many cases you will need to loop through both rows and columns. In some complex examples, you will need to additionally loop through each statistic.


To find the number of rows or columns in a table that you need to iterate over, we use the below references:

table.numberRows
table.numberColumns

A basic for..loop containing our formula can be written like below:

for (var row = 0; row < table.numberRows; row++) {
    for (var col = 0; col < table.numberColumns; col++) {
      values[row][col] = values[row][col] * 10
    }
}
  • We loop through each row from index 0 until we reach the number of rows in the table.
  • While looping through the rows, we also loop through the columns in similar fashion.
  • values[row][col] begins in the first iteration by returning the average for row 0 - column 0, that is, the first table cell from the top left.
  • We then tell it to multiply this value by 10 so that our values array now stores the updated values.

In the case of right and below statistics, we don’t have both row and column parameters, so we only iterate over one of these dimensions.

Right:

for (var row = 0; row < table.numberRows; row++) {
    values[row][0] = values[row][0]*10
}

Below:

for (var col = 0; col < table.numberColumns; col++) {
    values[0][col] = values[0][col]*10
}

Set statistic values

The final part of the code is to replace the Average with these values by setting the table statistic as follows:

table.set("Average", values);

As before, the equivalent code for setting right and below statistics would be:

right_table.set("Average", values);
below_table.set("Average", values);

It’s important to remember that when replacing statistic values, you must "set" the table with an array of the same dimensions as the values you "get" from the table. You must also use the original statistic name. See How to Access Statistics from a Table in a Rule for more details.

 

If you wish to add these updated values to your table as an additional statistic rather than simply update the original one, you can nominate another available statistic of the same type. This means if the original statistic is a percentage-based statistic then you need to use one that is also a percentage. In this case, we could use Mode as this is an available statistic that is not needed for our table.

Naturally, if we are using a statistic that doesn’t match the original name then this would be confusing! What we can then do is translate the statistic name at the end of our code. The below changes Mode to Custom statistic:

table.set("Mode", values);
form.setTranslation("Mode", "Custom statistic");

mceclip3.png

Method 2 - Add statistic values as a new row/column

Table calculations can often involve retaining the existing statistic values but performing some sort of calculation that gets added as a new row or column in your table.

Let's look at an example that calculates the difference between the first and last non-NET rows (i.e. 18 to 24 and 65 or more).

mceclip1.png

We begin by finding the last row index and adding a table row called Custom calculation:

includeWeb("Table JavaScript Utility Functions");
var last_index = table.numberRows;
insertRowAfterComplete(last_index-1, "Custom calculation");
  • We first reference the Table JavaScript Utility Functions library.
  • We work out the number of rows so we then have the index to insert the new row (in this case, at the bottom of the table).
  • Then we use the insertRowAfterComplete function to specify the position and name of the new row.
  • By default the statistic values for the new row will be missing.
  • Note, we can add a new column instead by swapping the Row and Column references.

We then get the statistic we want to recompute:

var stats = table.get("Column %");

Instead of looping through each row and column, we only need to loop through the columns using the row index (last_index) where we want to push the new values:

for (var j = 0; j < table.numberColumns; j++) {
    stats[last_index][j] = stats[0][j] - stats[8][j];
}

As j iterates through the loop, we can fix the row arguments of the array to reference the first and ninth rows.

Note, the reverse of this for adding a new column would be:

for (var j = 0; j < table.numberRows; j++) {
    stats[j][last_index] = stats[j][0] - stats[j][8];
}

Finally, we assign the new statistic values back to the table:

table.set("Column %", stats);

As the additional row was added prior to getting the values, the stats array now includes the correct array dimensions to return to the table.

mceclip2.png

 

Next

How to Modify Table Rows and Columns Using a Rule

 

Later

How to Modify Table Cells Using a Rule

How to Create User Input Fields in a Rule

How to Add Table Validations to Rules

How to Reference and Calculate Tables in a Rule

How to Add Table Spans Using a Rule

How to Add a Custom Table Footnote

How to Custom Sort Tables

 

See Also

How to Use Rules in Displayr

How to Customize a Rule

How to Create a Custom Rule

How to Access Statistics from a Table in a Rule

How to Work with JavaScript Arrays and Loops

 

Related articles