Why formulas?

Formulas provide a way to transform data in real-time so users can see calculated values and normalised data immediately.  A formula can be used for simple conversions (Eg. acres to hectares or m^2), or for calculating the days to a certain growth stage from an entered date or calculating the effective, humidity adjusted and normalised (t/ha) marketable yield per plant.

By using formulas to normalise data, everyone can immediately use and compare values across trials, irrespective of the country, local product or setup used.  

 
Derived traits

QuickTrials supports formulas in a feature called “Derived Traits”.  A derived trait is calculated by the system, based on the value of other traits.  For example, a measured trait could be entered in the units Kg, with the plot size in m^2 and a derived trait could be used to automatically calculate the value in t/ha or g/m^2.  This makes it easier to compare values across trials directly, without needing to manually calculate conversions every time.

Derived traits also go further than this, with support for the following:

  • Standard math operations: Eg. + – * /
  • Nesting: use functions / math inside of other functions
  • Unit checking: The system flags an error if units do not match the physical entity and therefore does not allow a conversion
  • Unit conversion: Automatic conversion of units is possible

Derived traits can also be used in the following ways: 

  • Templates: Derived traits can be incorporated into templates, making it easy to standardize trials and values
  • Real time updates: Derived traits are immediately calculated and added to the data warehouse when data is entered. This makes it possible to build reports with 3rd party software directly from normalized values.
  • Data visualization: Derived traits are shown directly in the data visualization graphs which allows monitoring of normalised values in real time

 

How to use derived traits

A derived trait can be added to a trial just like normal measurements. Setting the trait type to “Derived” will show one field for the formula and one for the unit.

The formula field is where the formula can be entered. At its simplest it will just reference another trait (eg. “WEIGHT_FRUIT_KG”) by specifying the respective code. This can be made more refined by referencing multiple traits and using aggregations, mathematical operations and other functions as needed.

The unit field is used to specify the resulting unit that is desired. The result of the calculation will be converted into this unit. If this is not possible, an error is shown in the Datavisualization -> Overview section.

The results of the derived trait are exportable via Excel or can be joined to the observations in the data warehouse.

A deeper dive

There are two functions that are worth mentioning especially. They are coalesce() and unit().

coalesce() – Allows to select values depending on their availability:

Name: Normalized Yield
Formula: coalesce(WEIGHT_KG, WEIGHT_FRUIT_G * NUM_FRUITS) / PLOT_SIZE_M2
Units: t / ha

Coalesce() runs by checking if the first argument exists. If it does, it is used. If it does not exist, the 2nd argument is used. As in this example, it allows to create a normalized derived trait that can be calculated via two different methods, depending on what traits exist in the trial.

The result of this calculation will always be converted to Tons per Hectare (t / ha) as indicated, independently from what units were used in the recorded values (KG or G in this case). This is possible because the system understands the physical significance of the units.

unit() – Allows to attach a unit:

Name: Height Index
Formula: HEIGHT_CM / (3.4 * unit(cm))
Units: None

This calculates an index value from a height measured and dividing through a constant. As we don’t want a unit attached to the index value, we need to give the constant a physical unit of the same kind that balances the incoming unit.

Derived traits with formulas provide a powerful way to enhance and optimise your trials in many different ways.  They will help to save time and make trial comparisons even easier.

Bejo Mexico case study

Bejo Mexico uses QuickTrials to streamline its trial operations and share data more easily with colleagues in other countries.  Trials involving 10 different types of crops are now managed efficiently with QuickTrials.  For more formation visit: https://www.quicktrials.com/bejo-quicktrials-case-study/

We hope you enjoy the new possibilities that formulas provide. If you have any feedback we are always happy to hear from you.
support@quicktrials.com

All the best from the QuickTrials team.