Skip to main content

History Calculations

History calculations give you the ability to create calculations that use data in previous entries for a specific field in an event form. This type of calculation is useful, for example, to track the average or total workload of an athlete over a specific period of time. This article covers:

For more information about the functions available for history calculations, refer to our article on Using Functions.

Types of history calculations

There are three types of history calculations:

  1. History calculations can be used to calculate numeric values using historic data entered by someone in answer to fields in the form. For example, the history calculation could use a formula that returns the sum of historical data entered in a numeric field for a specific period as well as the current data entered in the field.
  2. History text calculations can be used to calculate values using historic data entered by a user in answer to fields in the form and return the results in text format. For example, the history text calculation could use a formula that displays "This is an excellent result" if the data entered in a numeric field in the current form is higher than the average historic data for that field.
  3. Historical date calculations can be used to calculate date values that match queries about historic data entered by a user in answer to fields in the form. For example, the historical date calculation field could use a formula that returns the date of the maximum historic value entered for a numeric field. Note that History date calculations do not recognize dates in text type fields (single line text or paragraph) so you should instead reference a Date field or Date calculation.

Syntax for history calculations

The general syntax for historical calculations is similar to other calculation types. However, to reference historical data in a calculation, add Historical in front of the field name. For example, past Training Load values would be referenced as Historical Training Load, as is shown in the examples below:

historicalsum(Historical Training Load)
lastvalue(Historical Training Load)

Remember that field names are case sensitive, so Training Load should not become Historical training Load or historical Training Load. 

In the examples above, history calculations were demonstrated using historical data only. The calculation result would not include the new Training Load entered in the record being viewed.

To include new data in a calculation of average training load, the formula would need to look like this:

historicalmean(Historical Training Load, Training Load)

The first variable, Historical Training Load, represents the previous entries. The second variable, Training Load, references the new data that is entered in the current form. The result of this calculation would be a fully up-to-date average of Training Load.

To avoid calculation errors, you must not name any of your event form fields using any syntax that may be contained in another historical calculation. When naming either of the calculations above, you would not use Historical Sum Training Load or Last Historical Training Load as the field name.

When referencing historical data it is also important to remember that Teamworks AMS excludes data from records that have been saved as drafts; a history calculation will only use data from records that have been saved as complete.

Controlling historical data

It is possible to control the number of records to be included in the historical calculation. This can be done within the Calculation tab using three different methods:

  1. Setting a date range.
  2. Specifying the number of records to include.
  3. Applying filters to the data source.

Time ranges, number of records and filters can all be used for the same historical calculation.

Setting a date range for the data source

Date ranges can be specified using the Date restriction part of the Calculation tab. The options available are:

  • All history: includes all past records.
  • All (history and future): includes all past records. History calculations cannot reference records saved after the current record, so while this option is included (as it is in all date restriction areas of AMS, like the performance history) it works the same way as All history.
  • Date range: limits the records to be included by setting a start date and an end date for the history calculation.
  • Last: allows the builder to choose a number of days, weeks, months or years to include in the history calculation. All counts start from the day the record is saved.
    • Days: using Last with 7 days on a Monday will not go past the previous Tuesday.
    • Months: takes the number of days in the previous month and use that as the range. For example, if a calculation is set to Last 1 month and the event is saved in February, it will be the same as using Last 31 days because January has 31 days. If an event is saved in March with the same settings, it will be the same as using Last 28 days because February has 28 days. This means that the calendar date of the start date is the same as the current date – February 12 to March 12, for example.
  • All upcoming: will not include any records. History calculations cannot reference records saved after the current record, so while this option is included, it is not relevant to history calculations.
  • Last custom: allows the builder to control where the week or month starts and ends. If a calculation uses the option Last custom 1 week, Tuesday-Monday, it will essentially reset every Tuesday. This means that if you are running a historicalsum on a Thursday it will only go back until Tuesday (two days) because that’s when the week starts. The same applies for months, except that it is possible to specify on which day the month starts. For example, if set to Last custom 1 month and the 10th day is specified, the calculation will reset on the 10th, so if a record is saved on the 12th, it will only go back two days.

Specifying the number of records to include

The number of records to include can also be specified using the Date restriction part of the Calculation tab. The options here are:

  • Include all
  • Include the most recent record
  • 2 most recent records → 9 most recent records

These options can be used in conjunction with a date range. For example, selecting 9 most recent records between June and November last year.

Applying filters to the data source

The last way to control the number of records included in a history calculation is by setting up data filters to match conditions to other fields in the form. Using the Match all option means the conditions of all filters must be met; Match any means any filter’s condition may be met.

Using a Training Load field again as an example, a coach might be interested in getting the average Training Load value for a particular time of the season, so the builder can add a history calculation as follows:

historicalmean(Historical Training Load, Training Load)

The builder can then set a filter specifying that the Time Of Season field must be Equal To Pre-Season. This filter would have the history calculation ignore any record that does not have Pre-Season selected as the time of the season.

Filters only apply to historical data, so the new data entered in the current form will not be filtered.

To filter the new data entered in the current form, you can include an if statement in the formula. The formula can be modified as follows:

historicalmean(Historical Training Load, if(Time Of Season = "Pre-Season", Training Load,""))

By including an if statement, the filtering criteria applied to the historical data is now also applied to the new data entered in the form. Filters in historical calculations can also use a condition called Match entered data, which allows the builder to create filters that adapt to the data being entered.

Using the same scenario, if the coach also wanted to know the average Training Load when the Time Of Season is recorded as In-Season, the builder would otherwise have had to create an identical historical calculation using that filter.

By setting the filter up so that Time Of Season should Match entered data we can simplify the results and match the option currently selected for Time Of Season to the relevant historical data.

Only use the Match entered data condition for single selection fields and option calculations to reduce the likelihood of including false negatives in the calculation result.

Steps for creating a history calculation

  1. Log into the builder interface.
  2. Select the Event forms tool.
  3. Create an event form or open an existing event form:
    1. If they don’t exist already, add the fields you want to reference in your history calculation.
  4. Select the appropriate type of history calculation from the Add question tab.
  5. Write the history calculation formula you want to use in the Calculation tab.
  6. If applicable, set a date range, specify a number of records or apply data filters for the history calculation.
  7. Save the form.
  8. Open the administration interface.
  9. Assign data permissions for the event form you created to your role.
  10. Open AMS.
  11. Create at least two records for the event form you created and save each with the event form date set to different past dates.
  12. Check whether the history calculation is working as expected.
  13. Return to the builder interface to make any modifications required.

It's important to remember that the results for historical calculations aren't displayed on initial data entry, they will only show after records are saved. If a history calculation doesn't include data from the current record, it will not show at all for the oldest (first) record because there is no historical data.

Related articles

Powered by Zendesk