Table calculations are used to perform calculations about a table in an event, profile or database form. Table calculations are only used to perform calculations about data within the table and should not be part of the table. This article covers:
- Types of table calculations
- Aggregating functions for table calculations
- Table calculation syntax
- Steps to build a table calculation
The fields called Total Session Load, Maximum Intensity and Total Sets below the table in the example screenshot above are table calculations. The first one is adding up entries in the Load column and the last one is adding up the Sets column. Note that these table calculations are not part of the table.
Types of table calculations
- Table calculations can be used to calculate numeric values, using aggregate functions, from data entered by a user into columns in a table in the form. For example, a table calculation field could use a formula that returns the average of all values entered into a table column.
- Table option calculations can be used to calculate values, using aggregate functions, from data entered by a user into a table in the form and return a result in option format. For example, a table option calculation field could use a formula that returns an option result of "Pass" when the sum of numeric entries into a column is higher than 60 or "Fail" when it does not.
- Table text calculations can be used to calculate values, using aggregate functions, from data entered by a user into a table in the form and return the results in text format. For example, a table text calculation field could use a formula that returns a result of “All criteria met” if the data entered into an option field column meets certain criteria.
- Table date calculations can be used to calculate values, using aggregate functions, from date data entered by a user into a table in the form and return the results in date format. For example, a table date calculation field could use a formula that returns the newest date from a column of date entries.
- Table duration calculations can be used to calculate values, using aggregate functions, from duration data entered by a user into a table in the form and return the results in duration format. For example, a table duration calculation field could use a formula that returns the sum of all duration entries in a column.
Aggregating functions for table calculations
Table calculations differ from simple calculations because they use an aggregating function to decide how the data in each table row should be treated. The aggregating function is used in conjunction with the formula entered in the calculation field.
In this example, the table field called Best 40m will be calculated using the minifpositive aggregate function to return the fastest 40m sprint time from a table that records multiple sprints. Note that the aggregation function options available are different depending on the type of table calculation you are using.
These are some of the most commonly used aggregate functions and their variants:
- Count will return the number of tables rows with data in them. If count is used in reference to columns containing text instead of numbers (exception: fields with scored options), wrap the field with the safe function: safe(Field name). Be mindful that when using the safe function empty cells will count as well. The countifpositive variation will ignore negative values. Count and countifpositive should only be used in the table calculation field type.
- To return the sum of data from a column, you can use the sum aggregate function. It also has a sumifpositive variation. These functions should only be used in the table calculation field type.
- Max, min, mean, median, mode, stddev, var (variance), sem (standard error of the mean) all operate in the same way as sum. These functions should only be used in the table calculation field type.
- Firstvalue will return the first value entered in a table column. It has variations like firstpositive, firstnonzero and others that do exactly what their name implies. These functions should only be used in the table calculation field type.
- Lastvalue will return the last value entered on the column. It has same variations as firstvalue. This function does not ignore empty cells. These functions should only be used in the table calculation field type.
- Lastenteredvalue is similar to lastvalue but will ignore blank cells. These functions should only be used in the table calculation field type.
- Textsummarise will summarize the text from each row of a table column and place a comma between each text. This function should only be used in the table text calculation field type.
- Whitespacetextsummarise is the same as textsummarise but will leave a space between the text instead of a comma. This function should only be used in the table text calculation field type.
Note that nthmax, nthmin, index and all of their variations do not operate as aggregate functions for table calculations.
Table calculation syntax
The example above is a simple table calculation that combines an aggregate function and a field name reference. However, table calculations can be more complicated if necessary. It is possible to create more complex table calculations like adding all Reps that were done at over 70% of Body Mass by selecting sumifpositive as the aggregate type and writing the calculation’s formula as follows:
If(Weight > (0.7 * Body Mass), Reps, -1)
In this example Body Mass is a field outside the table and the formula will return either the Reps for that row or -1 depending on the condition being met or not. Then, because we are only adding up the positive values, the rows where the condition was not met will be ignored.
A useful trick is the use of the row function. This function simply returns the number of the row but by targeting specific rows it opens up a great range of possibilities. For example, using the table calculation field with the sumifpositive aggregate and writing the following formula would allow the builder to discard the Load values in the first two rows and sum all other positive values for Load.
If(Row() > 2, Load, -1)
The most common table text calculation function is textsummarise, which allows the builder to extract all the text in a column. With this function all exercises performed in the first example could be extracted as a text string. This would avoid having multiple rows for the same record appear in reports.
Table option calculations are usually used to extract the latest status of something. For example, a physio treatment record would usually have a table to record each individual treatment for a particular injury. After every visit the physio has to update the status of the injury, which is normally an option field. Let’s assume that the options are simply Open or Closed. A table option calculation could be created by setting the aggregate type as lastenteredvalue and entering the field name into the calculation text box:
It is necessary, as with option calculations, to set the options of the table option calculation exactly the same as the options of the fields being referenced (Injury Status). When properly built this calculation would result in the current injury status.
Steps to build a table calculation
- Log into the builder interface.
- Select the Event forms tool.
- Create an event form and add the fields you wish to use, choosing the Table entry mode option for each field.
- Select the type of table calculation needed on the Add question tab and add this field, ensuring the calculation is not part of the table.
- Select an appropriate aggregate function for the calculation from the drop-down list of aggregate functions.
- Write the calculation formula you wish to use in the Calculation tab
- Click the Preview button and test whether the calculation is working as expected
- Exit preview mode and either continue working on the form or save the form.