History calculations give you the ability to create calculations based on the 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:
- Types of history calculations
- Steps for creating a history calculation
- Syntax for history calculations
- Historical functions
- Combining history calculations
- Controlling historical data
Types of history calculations
There are three types of history calculations:
- History calculations can be used to calculate numeric values using historic data entered by a user 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.
- 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.
- 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.
Steps for creating a history calculation
- Log into the builder interface.
- Select the event forms tool.
- Create an event form or open an existing event form:
- If they don’t exist already, add the fields you want to reference in your history calculation.
- Select the appropriate type of history calculation from the Add question tab.
- Write the history calculation formula you want to use in the Calculation tab.
- Save the form.
- Open the administration interface.
- Assign data permissions for the event form you created to your role.
- Open the user interface.
- Create at least two records for the event form you created and save each with the event form date set to different past dates.
- Check whether the history calculation is working as expected.
- 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.
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 Smartabase 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.
There are a specific set of functions designed to be used in history calculations, these are:
As best practice, use these instead of their regular variations (sum, max, etc.). However, these are not the only functions available for this calculation type. Any function that uses a range of numbers as an argument can be used as well, for example:
Note that the available functions are different for each type of history calculation.
Using percentiles and percent ranks in history calculations
A percentile is a measure used in statistics indicating the value below which a given percentage of observations in a group of observations falls. For example, the 5th percentile is the value (or score) below which 5% of the observations may be found. In Smartabase, you have two different percentile options: percentile and percentrank. In this example, we want to calculate the 5th percentile of the athlete's current Total Wellness Score in a history calculation. To do this, we will use the following formula:
historicalpercentile(Historical Total Wellness Score, Total Wellness Score, 0.05)
Here, the first two elements describe the information we want to find the percentile of, and the final value indicates the specific percentile we want to return. If the athlete has returned Total Wellness Score values of 4, 6, 2, 10, 5 and 8 (today's score), the 5th percentile would be returned as 2.5. In other words, a score of 2.5 or lower would be in the lowest 5% of all Total Wellness Score values for this athlete.
You may then like to use these calculated percentiles to create a flagging system, based on what percentile the athlete's current score sits in. Similarly, the historicalpercentrank function can be used to calculate where the athlete sits as a percent rank in relation to their past scores. This ranking works on a 0-1 basis, with one representing 100%.
If we want to find the percent rank of Total Wellness Score, we can calculate it as follows:
historicalpercentrank(Historical Total Wellness Score, Total Wellness Score, Total Wellness Score)
For this function, the final element describes the field we want to return the percent rank of, with respect to the first two elements. With a Total Wellness Score today of 8, the percent rank would be calculated as 0.8 (i.e. in the 80th percentile of their historical data).
Using the exponentially weighted moving average function
One approach to monitoring training load is to calculate an exponentially weighted moving average (EWMA). The historicalewma function calculates the EWMA of a value based on the number of days in the decay period (i.e. the decay constant) and the event date as a date calculation.
To calculate the EWMA of Total Training Load using a decay constant of seven days, we will use the following formula:
historicalewma(Total Training Load, Historical Total Training Load, Date Calculation, Historical Date Calculation, 7)
A date restriction on the data is also required to calculate EWMA over the period of time. The date restriction should include several times more days than the delay constant to ensure more accuracy in the EWMA value.
Combining history calculations
A good example of when multiple history calculations can be effectively combined is when calculating personal bests.
In this example, we are calculating the fastest time an athlete has ever run a 100m race in an event form that already contains fields called 100m Time and Race Date. First, a history calculation called Personal Best can be built to find the fastest time an athlete has run 100m in:
historicalmin(Historical 100m Time, 100m Time)
Then by using the function valuewhentargetmatches in a historical date calculation, it is possible to retrieve the date associated with the minimum 100m Time value. This formula would be written as:
valuewhentargetmatches(Personal Best, (Historical 100m Time, 100m Time), (Historical Race Date, Race Date))
This formula will look up when the Personal Best calculation result matches the 100m Time field and get the Race Date of that record. Note that Race Date has to be a date field, historical date calculations do not recognize dates in text field types. Other functions useful in combination with historical date calculations are lastvalue and firstvalue.
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:
- Setting a date range.
- Specifying the number of records to include.
- 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 Smartabase, 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.