Table calculations are used to perform calculations about a table in the form. Table calculations are only used to perform calculations about data within the table and should not be part of the table. The most recent version of Smartabase allows users to create numeric, option, text, date and duration 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.

Table calculations differ from regular 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 (see below, **Example: Aggregate functions)**. 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: questions 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.**Sum****sumifpositive**variation. These functions should only be used in the table calculation field type.**M****ax**,**min**,**mean**,**median**,**mode**,**stddev**,**var**(variance),**sem****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****firstvalue**. This function does not ignore empty cells. These functions should only be used in the table calculation field type.**Lastenteredvalue****lastvalue**but will ignore blank cells. These functions should only be used in the table calculation field type.**Textsummarise****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. See the aggregate reference list for a full list of aggregate functions.

**EXAMPLE: TRAINING SESSION TABLE CALCULATIONS**

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.

**EXAMPLE: WRITING TABLE CALCULATION FORMULAS**

The first example given of table calculations uses simple calculations that combine an aggregate function and a field name reference. However, table calculations are not limited to being this simple. 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 for any builder 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 question. 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:

**Injury Status**

It is necessary, as with option calculations, to set the options of the table option calculation exactly the same as the options of the question being referenced (**Injury Status**). When properly built this calculation would result in the current injury status.

**EXAMPLE: AGGREGATE FUNCTIONS**

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.

**HOW TO: BUILD A TABLE CALCULATION**

- Log into the builder interface
- Select the
module*event pages* - Create an event form:
- Add the questions you wish to use, choosing the
**Table**entry mode option

- Add the questions you wish to use, choosing the
- 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
- 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
- Save the form