Table Calculations

Table calculations are used to to perform calculations about a table in a form. You ONLY use them to perform calculations about data within a table. They are NOT used as part of the actual table. If you need more information about tables, see the lesson about setting up a Table in a form.

Table calculations are used exclusively to calculate numbers, text and options calculations for columns of data for a specific field type.

Table calculations are used exclusively to calculate numbers, text and options calculations for columns of data for a specific field type.

The example here shows that table calculations are being used to calculate the number of surgeries as well as the total days in hospital.

Table calculations are very important because they need to be designed to accommodate a variable number of rows of data.  The max, mean and average calculations and even the normal calculations will NOT allow you to calculate data in a table correctly. The following steps outline common uses of table calculations.

You can generate Numeric, Option and Text calculations for table columns

You can generate Numeric, Option and Text calculations for table columns

Exactly the same as we have different table, text and option calculations, we can calculate out text, options and numeric answers for fields that are built into tables.

Table Calculation: allow you to perform a numeric calculation about field columns (with any number of rows)

Table Calculation: allow you to perform a numeric calculation about field columns (with any number of rows)

This example shows that a table has been built to capture profile data about hospitalisations for an athlete . The hospitalisations table has been set up using a date, single  line of text, yes no and a number field (days in hospital).

For this example we want to add up the number of days in hospital.

Click on the Table calculation (because we are performing an equation for a field in a table that is a number)

Click on the Table calculation (because we are performing an equation for a field in a table that is a number)

Name the field and ensure that it's question settings are set as Normal and NOT as a table

Name the field and ensure that it's question settings are set as Normal and NOT as a table

Set up the Advanced Properties and then select the type of calculation you want to perform using the Aggregrate

Set up the Advanced Properties and then select the type of calculation you want to perform using the Aggregrate

The aggregrate is simply the calculation that you want to perform on a specific table field. For this example we want to calculate the total days in hospital so we need to select the "sum" aggregrate.

There a numerous numeric calculations that you can perform on a table row, and if your have any queries about the specific aggregrate functions, please ask us to include more in this manual by leaving a comment in the comments section of this lesson.

Next, ensure that the field (table column) is included in the calculation area (as shown in the image in the step below)

Choose the field that you want the calculation to be performed on

Choose the field that you want the calculation to be performed on

Either write in the name of the field (with the exact spacing and case) or,

#1: Click on the "Add Question" drop down

#2: Select the field name

#3: Click the green plus

Check the calculation captures the data that you want in the preview page and save any changes.

Check the calculation captures the data that you want in the preview page and save any changes.

The table calculation was set to sum the days in hospital field (as shown here)

To run a calculation on the number of surgeries that the athlete has, the Surgery Performed field (as yes no field) in the actual table was set up with scored options

To run a calculation on the number of surgeries that the athlete has, the Surgery Performed field (as yes no field) in the actual table was set up with scored options

A Table Calculation was then added to the form that used the aggregrate Sum for the field Surgery Performed

A Table Calculation was then added to the form that used the aggregrate Sum for the field Surgery Performed

This means that the any time yes is selected in the surgery performed field, the score will equal 1, which will be summed.

The Preview of those table calculations shows that the field Total Surgeries is being summed where Yes =1 and No =0

The Preview of those table calculations shows that the field Total Surgeries is being summed where Yes =1 and No =0

YOu can see here that table text calculations can be used to calculate out numeric field in a table and even option field (but ONLY if they had scoring set for each option).

Table Text calculation, building on the hospitalisations example

Table Text calculation, building on the hospitalisations example

For this example we are going to add in a table text calculation to summarise all of the reason's for hospitalisation. Click on "Table Text Calculation" to add in the text calculation about a table field.

Set up the field settings and ensure that the question settings are normal for the table text calculation

Set up the field settings and ensure that the question settings are normal for the table text calculation

Select the field that you want to run the text calculation for and add it or type it into the calculation area

Select the field that you want to run the text calculation for and add it or type it into the calculation area

Either write in the name of the field (with the exact spacing and case) or,

#1: Click on the "Add Question" drop down

#2: Select the field name

#3: Click the green plus

Select the Aggregrate options for a table text calculation

Select the Aggregrate options for a table text calculation

You will have the following options to use on the text field from your table (these are displayed in the image in the step below).

count: will return the number of rows that have been selected (even if there is not data in some of those rows).

customtextsummarise: will summarise the rows of data into one line with no "," in between each response

text summarise will summarise all of the rows of data and place a , in between the each response

first value will take the first value in the table (even if it is blank)

last value will take the last value in the table (even if it is blank

last entered value will take the last entered value on the table column, which may pull data from another row which is not the last one

For this example we want to set up summarise with a "," between each reason so we would use the text summarise aggregrate. However, all of the different options for a text field have been added to show you an example of the fields you can use to get the specific information you need from each table you create.

An example of all of the different types of table text calculations performed on the Reason for Hospitalisation Field

An example of all of the different types of table text calculations performed on the Reason for Hospitalisation Field

Table Option Calculation's are important for showing different states or returning option based feedback for option fields in a table

Table Option Calculation's are important for showing different states or returning option based feedback for option fields in a table

The example here shows that a for an injury status field and assessment table, the option calculation is used to pull through the last entered value in the Injury Status (option field), which will be used as to show the Availability for that athlete.

To Add an Option calculation about a selection based field in a table click on "Table Option Calculation"

To Add an Option calculation about a selection based field in a table click on "Table Option Calculation"

Name the field and ensure its question settings are on normal

Name the field and ensure its question settings are on normal

Now set up the options so that they are the same as the options from the table field you are using (as shown in the image in the step below).

Copy the options from the existing option field that you are using in your calculation and click update for the options

Copy the options from the existing option field that you are using in your calculation and click update for the options

You do not need to update the scores of the options, but make sure you set the best value on the Advanced Properties section (as shown below)

Set up the Advanced Properties

Set up the Advanced Properties

This field is being set up a as a status indicator because it is injury status, but most of your option calculations will NOT be set as a Status Indicator field.

Now set the option calculation, which will pull the aggregrate of the lastenteredvalue for the field Injury Status

Now set the option calculation, which will pull the aggregrate of the lastenteredvalue for the field Injury Status

The aggregrate options are the same as those shown for text table calculations.

Again, set the aggregrate and then select the field it applies to. For this example it will apply to the Injury Status field in the assessment table.

The preview of the form shows that as the injury status of the athlete progress the availability field (option table calculation for last entered value) updates

The preview of the form shows that as the injury status of the athlete progress the availability field (option table calculation for last entered value) updates

The availability field calculates the last entered value to give an update of the injury status. More complex table calculations (numeric calculations) are also used in this Injury Assessment table. Both the duration of illness and the number of days unavailable are table calculations

Additional Table Calculation (numeric) examples: injury duration and days unavailable

Additional Table Calculation (numeric) examples: injury duration and days unavailable

Number of days unavailable for work is a table calculation that calculates the sum of the Duration field (a date difference duration from the date from the assessment data to today's date ), but it only sums the durations when the injury status is equal to not able to work (see the image below).

Table calculation using an "If then" equation

Table calculation using an "If then" equation

The example here show that for the days unavailable field, the table calculation will sum all of the durations when injury status = "injury/Ill not able to work". This is achieved using an If, then equation.

The Total duration of injury is also a table calculation using a different data duration field that calculates the last entered value for a different date duration

The Total duration of injury is also a table calculation using a different data duration field that calculates the last entered value for a different date duration

This table calculation is set to show the last entered value in the Column Date Duration2 ( that calculates out the date difference between the date of first symptoms of the injury, until the date of assessment), but it will only calculate this when the Injury Status field is = "Able to work, not injured/ill".

Regardless of what injury states the athlete has been in, the injury duration table calculation will only calculate when the state is equal to "Able to work, not injured/ill" and it will return the last value for this in the table, as an athlete may have on-going treatment when when they are not injured.

Table Calculations: Example from a resistance training form

Table Calculations: Example from a resistance training form

The image here shows a resistance training form, where total training load and average intensity are both table calculations which are used to summarise the resistance training form.

The different table calculations used in the resistance training form

The different table calculations used in the resistance training form

Total Session Load: table calculation

-Aggregrate: sum

-Field: Load

Average Intensity: Table calculation

-Aggregrate: mean if positive

-Field: Intensity

Total Sets: Table calculation

-Aggregrate: sum

-Field: Sets

More uses for Table Text calculations to show important data for a form, and to be used as a summary field.

More uses for Table Text calculations to show important data for a form, and to be used as a summary field.

Table text calculations can be used in conjunction with table calculations to summarise data from a form and use it as a summary in the sidebar. The image here shows for a bloods form, a Summary field (table text calculation) has been set up to give users a brief overview of the blood test and the result for each measure taken.

To set this up on the builder in the form, a text calculation was added to the table, and then that was used in the table text calculation

To set this up on the builder in the form, a text calculation was added to the table, and then that was used in the table text calculation

The Blood results field in the table was set up as a text calculation (to concatenate (Blood Test, ",",Result)), and this field is set to hidden and default show in tables false so that it doesn't appear on the enter data page or the athlete history page. It is ONLY used to calculate the Summary field.

The Summary Table text calculation was set up to text summarise the Blood Results field. This field's advanced properties were set as to summary field to show through onto the Sidebar (as shown in the image in the picture above).

If you need more examples of table calculations, then please ask in the comments box below

0 Comments

Add your comment

E-Mail me when someone replies to this comment