Introduction
This article describes how to modify an existing table statistic and create a new one using a custom Rule.
Requirements
- A table with a Rule applied.
- This article assumes you are already in the Automate > Custom Rule or Edit Rule > Edit JavaScript Rule view specified in How to Use Rules in Q and How to Customize a Rule.
- You have read How to Create a Custom Rule and How to Access Statistics from a Table in a Rule.
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.
As 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[col][0] = values[col][0]*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);
bottom_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");
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).
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
insertRowAfterCompletefunction 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.
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
See Also
How to Access Statistics from a Table in a Rule
How to Work with JavaScript Arrays and Loops