CALCULATED QUESTIONS

WARNING: DO NOT USE A FORM WITH CALCULATED FIELDS ON A PPC DEVICE. This question type functions in Mobile Auditor PC/Compact/Web only.

This question type allows you to specify a formula which is used to calculate the answer based on the answer to one or more other questions on an audit form.

For example, let's say question #1 asks for the total number of employees under the age of 18, and question #2 asks for the total number of employees over the age of 18. Question #3 could calculate the sum of those two answers.

The value for the calculated question is populated by pressing the CALCULATE button found on each calculated question in Mobile Auditor PC, Compact, or Web. Calculated questions do not accept input from the auditor. Instead, the auditor must click the CALCULATE button to populate the answer field associated with the question. Otherwise, the question remains unanswered, so if the question is required, the audit cannot be completed.

Invalid calculated answers are shown as ERROR and an audit cannot be completed as long as there is an ERROR answer in a required calculated question. For example, trying to run the calculation before answering all of the questions referenced in the formula will result in the error message being displayed.

When creating the formulas in the Question definition screen during setup, a VALIDATE link is available so that the formula for each calculated question may be validated. Once you've entered the formula, be sure to click the VALIDATE link to verify that the equation is valid.

Answers to NUMERIC, SPINNER, or other CALCULATED question types may be used in the formulas contained in a calculated question.

 

*NOTE: Always use grouping/aggregate functions if you are including any reference that will result in more than one question match.

When using question and category references in equations, the final values come out in a comma delimited string (answers are separated by a comma). Therefore, if there are two or more questions in the category or subcategory that have the same question reference, group the question reference within a grouping/aggregate function (SUM, MIN, MAX, AVG). Otherwise, the resulting formula may be invalid.

For example, if there is only one question that has the question reference QREF1 (with the answer to this question is "4"), and the equation QREF1+12 is used, the resulting equation string would be:

4+12

BUT, if there are three questions that have the question reference QREF1, the resulting equation string could be:

12,3,4+12

This is not a valid equation. When you need to include a reference with more than one matching question, use a grouping/aggregate function and write the equation like this:

SUM(QREF1)+12

This would result in the following:

SUM(12,3,4)+12

This is a valid formula.

 

FORMULA SYNTAX

@...@ This set of characters is used to indicate that the string in the middle is a Question Reference. Using this character set within in a formula will cause all of the matching questions in the current category and its subcategories to be used in the equation.

Examples:

  • AVG(@QREF1@) - This will return the average value from all of the questions with question reference QREF1 within the current category and its subcategories.
  • SUM(@QREF1@, @QREF2@) – This will cause all of the valid questions with question reference QREF1 within the current category and its subcategories to be added to the sum of all of the valid questions with question reference QREF2 within the current category and its subcategories.
  • @QREF1@/@QREF2@ - This formula may only be used if there is only one matching question for each question reference. The result will be the value found on QREF1 divided by the value found on QREF2 (within the current category and its subcategories).


Remember to use grouping/aggregate functions if you are including any reference that will result in more than one question match. See note above*.

 
%...% This character set will be used to indicate that the string in the middle is a Category Reference. Using this character within a formula will cause all of the questions within the current category or its subcategories (based on the category reference given) to be pulled into the equation.

Examples:

  • SUM(%CREF1%, @QREF1@) – This formula will cause all of the valid questions in the current category or its subcategories (starting where the category reference CREF1 is found) and the questions from the current category and any of the subcategories with a category reference CREF1 to be summed together.
  • AVG(%CREF1%) – This will get the average value for the valid questions within the specified category and its subcategories.
  • %CREF1%-%CREF2% – This formula may only be used if there is only one matching question in each of the categories referenced. This will cause the sum of the values in the categories (and their subcategories) with a category reference CREF2 to be subtracted from the sum of the values in the category (and its subcategories) with the reference CREF1. The recommended way to write this formula would be: SUM(%CREF1%)-SUM(%CREF2%).


Remember to use grouping/aggregate functions if you are including any reference that will result in more than one question match. See note above*.

 
$...|…$ This character set is used to indicate that the we are looking for one specific question in one specific category. The syntax is as follows: $CREF|QREF$

The first question that has a matching question reference that is found in a category with a matching category reference will be used in fulfilling the equation.

Examples:

  • $CREF1|QREF1$ – This formula would find the value given for the first question with the question reference “QREF1” found within the first category with the category reference “CREF1”
  • SUM(@QREF1@,$CREF1|QREF2$) – This formula would sum up all of the values from the questions that matched the question reference QREF1 in the current category and its subcategories and add to them the value found in QREF2 in CREF1.

     

Remember to use grouping/aggregate functions if you are including any reference that will result in more than one question match. See note above*.

 
^ This character can be placed in front of a category or question reference to break free from the current category and its subcategories, and search through the entire audit result.

Since the $...|…$ formula already searches the whole form, this character cannot be used within that formula.

Examples:

  • SUM(^@QREF1@,1.34) – This formula will return the sum of all of the questions in the form with question reference QREF1 plus the number 1.34.
  • SUM(^%CREF1%)/AVG(@QREF1@) – This formula will return the sum of all of the valid questions found in any category on the form with a category reference CREF, divided by the sum of the questions in the current category (or its subcategories) with the question reference QREF1.

Remember to use grouping/aggregate functions if you are including any reference that will result in more than one question match. See note above*.

 
` The agave (`) character can be placed in front of any question or category reference to limit the search to the current category only (and not the subcategories).

Since the $...|…$ formula looks for a specific question in a specific category, this character cannot be used within that formula.

Examples:

  • AVG(`@QREF1@) – This will return the average value from all of the questions with question reference QREF1 within the current category (does not look in the subcategories).
  • AVG(`%CREF1%) – This formula will return the average value from all of the questions within the current category if the current category’s category reference is CREF1.

Remember to use grouping/aggregate functions if you are including any reference that will result in more than one question match. See note above*.

 
AVG(…)
SUM(…)
MIN(…)
MAX(…)
 These will cause the values found within the brackets to be parsed and come up with the average, sum, minimum, or maximum value within the set. The values within the parentheses must be comma separated, and if there are nested equations, they must be contained within their own set of parentheses.

Examples:

  • AVG(@QREF1@, 5) – This formula will give the average of the values found by @QREF1@ and the number 5.
  • SUM(@QREF1@,%CREF1%,(@QREF4@-@QREF3@)) – This formula will return the sum of the values found by @QREF1@, %CREF1%, and (@QREF4@-@QREF3@).


Remember to use grouping/aggregate functions if you are including any reference that will result in more than one question match. See note above*.

 
+
-
*
/
 These operators will cause two values to be added, subtracted, multiplied, or divided. If there are nested values, they must be encompassed by parentheses.

Examples:

  • @QREF1@+@QREF2@
  • @QREF1@-AVG(@QREF2@, @QREF3@)
  • $CREF1|QREF1$+(@QREF1@/@QREF2@)


Remember to use grouping/aggregate functions if you are including any reference that will result in more than one question match. See note above*.