Add dynamically new columns using the addcolumn modification function
It is possible to add a new column to a table using a rule, which could be handy when the input table data are not controlled by you. In this tutorial we discuss how to add two more columns in order to display the Sum and the Average values of the existing columns.
First of all, you need to understand conceptually the steps involved in this process:
- Add new column to host the sum (total) values using the addcolumn modification function.
- Add new column to host the average values using the addcolumn modification function.
- Calculate the sum of the values, using the add built-in calculation function.
- Calculate the average of the values, using the avg built-in calculation function.
- Format the numeric values, using the built-in data format functionality (used for the average values, which do have a decimal part).
- Apply various other rules for styling or other purposes.
One important factor is the order of execution, therefore for all rules that will add to the ruleset and change the structure of the table or modify the values we will set the priority parameter. For instance, we need to add first the average column before applying the avg caclulation function.
How to call the addcolumn modification function
You can call it using the following parameters:
- Function name: addcolumn
- Function args: column id:position:delimiter:column values
Here is a brief explanation of the arguments:
- column id: The column id that will be used as a reference when adding the empty column. It can be right before it or after that. It should be an integer number between 1 and the number of columns.
- position: it can be either after or before and it's used along with the column id to specify the exact location of the newly inserted empty column.
- delimiter: the delimiter of the column values.
- columns values: the values that could be used to populate the new column. It can be an empty string if you don't want to add any values (empty column), or just a few values. In other words, it is not necessary to populate ALL the column values.
How to call the calculation functions add and avg
For a detailed description please have a look at the documentation. The most common way of calling them is using parameters similar to:
- Arguments Range: [SAME],[1-3]
The arguments range is in the form [Row range],[Column range], where Row range is usually SAME (meaning "same row as me") and column range is the exact range of the columns that you want to calculate (e.g. add). For instance, in the example we presented columns 1,2 and 3 will be added. You could also use a dynamic parameter such as P1 which means "previous column".
Tip: If you want to avoid calculating and replacing certain values, use the element section rule parameter to exclude certain columns/rows. For instance, if you set the element section to 2-L1 the top header row will not take part in the calculations.