User Tools

Site Tools


example-formulas

Example Calculated Field Formulas

Almost every tracker will have some calculated fields, and these calculated fields also provide flexibility to uniquely customize trackers.

The use of calculated fields may be different from one tracker to the next, but there are some common uses for calculated fields and their formulas will look similar. In this regard, the following examples represent some common types of calculated field formulas and can serve as a basis or recipe for customizing trackers.

Weighted Checklist

For project tasks whose completion is determined by a checklist (i.e., “checklist driven tasks”), there typically needs to be a calculated field to track which checklist items have been set and the progress towards completion of the overall task.

For example, the image below shows an example checklist displayed on a form:

For this checklist, a formula to compute the overall progress could be the following:

    IF(ISTRUE([ProgressProblemReproduced]),0.1,0) + 
    IF(ISTRUE([ProgressSolutionIdentified]),0.3,0) +
    IF(ISTRUE([ProgressSolutionImplemented]),0.5,0) +
    IF(ISTRUE([ProgressSolutionDelivered]),0.1,0)

Each checklist item is associated with a field, but is edited using a checkbox form component as shown above. The formula only includes the weight for the given checklist item if the underlying field is set to a true value.

Calculate When a Checklist Item was Checked

A checkbox can be used to mark the completion of a task. In this case, it is also useful to know when the task was marked complete.

Instead of requiring the completion date to be set manually, a calculated field can be used to determine when the checkbox was marked true. The WHENTRUE() function is intended for these types of calculations; for example:

    WHENTRUE([ProgressSolutionDelivered])

The WHENTRUE() function is not limited to taking single field values as parameters. Any parameter which evaluates to a true or false logical value can be given as a parameter.

example-formulas.txt · Last modified: 2018/02/24 00:11 by sroehling