Descriptions of Mathematical Operators
Tips on Building Custom Formulas
A formula is an algorithm that derives its value from other fields, expressions, or values. Formulas can help you automatically calculate the value of a field based on other fields. AlertBoot supplies some standard formulas (e.g., Summation, Averaging, etc.) to new reports. In addition, you have the option of creating your own custom summary formulas to calculate additional totals based on those existing default summaries in each report.
Note: Formulas cannot be shared across multiple reports.
To create a new custom formula:
Click the New button on the right side of the Custom Summary Formulas bar on the Step 3: Select the Information to summarize page of the Report Wizard.
The Formula Builder window is displayed.
Enter a unique label for your formula as it will appear within the report.
Enter a description for the formula.
From the Format dropdown list, select the output data type for your formula. The data type determines the formatting of the value that you expect to be returned from that formula:
|
Number |
Returns a positive or negative integer or a decimal of up to 7 digits |
|
Percentage |
Returns a number in percent format followed by a percent sign. Percent data is stored as a decimal divided by 100 (e.g., 90% = 0.90) |
|
Currency |
Returns a number in currency format with a dollar sign |
From the Decimal Places dropdown list, select the number of decimal places to display for currency, number, or percent data types.
Begin building your formula. Depending on how you decide to create your formula, you may need to start with an Operator first to complete your custom formula:
There are three different parts to your custom formula:
|
Fields |
Preset fields that can be used as part of a custom formula: a) Select one of the fields from the Select Field dropdown list
b) Select a Summary Type (See Descriptions of Summary Types).
c) Click the Insert button to the right of the Summary Type field.
|
|
Operators |
These are mathematical functions that are part of your equation. Select an Operator by clicking the appropriate button.
See Descriptions of Mathematical Operators for more information.
|
|
Number Input |
This is an open field to add a number to use as part of your custom formula.
a) Enter the number you want to add to the formula b) Click the Insert button
|
Repeat these steps as many times as necessary until your formula is complete.
Click Validation Syntax to check your formula for errors. Possible syntax errors will be listed in red at the top of the window.
If you made a mistake and need to re-create the formula from scratch, click the Clear Formula button at the bottom of the window.
Click the Done button to add your formula.
Note: Clicking the Done button will not save the formula. The formula is included in the report wizard; it will be saved when the report is saved.
For every field you select in a custom formula, you can select a summary type to use for the data.
Certain summary types do not necessarily apply to certain types of fields.
|
Sum |
The summary value of numerical data in a field or grouping of fields |
|
Average |
The average of data in a field or grouping of fields (Mean) |
|
Largest Value |
The largest value of data in a field or grouping of fields (Maximum) |
|
Smallest Value |
The smallest value of data in a field or grouping of fields (Minimum) |
|
Standard Deviation |
A statistic that measures the spread of data from the sample's average. To simplify, the higher the Standard Deviation, the more fluctuations you have in your sample data. The lower the Standard Deviation, the closer your sample data is to your average. A high standard deviation for the number of failed login attempts for a given device can indicate either foul play or an attempt by someone to guess at a device's password. |
|
Count |
Similar to a sum, but used for non-number items, such as activations or invoices. |
The following is a list of the mathematical operators used when creating custom formulas.
|
Symbol |
Math Operator |
Description |
|
|
ADD |
Calculates the sum of two values. Use: value1 + value2 Replace each value with fields or other numeric values. |
|
|
SUBTRACT |
Calculates the difference of two values. Use: value1 - value2 Replace each value with fields or other numeric values. |
|
|
MULTIPLY |
Multiplies its values. Use: value1 * value2 Replace each value with fields or other numeric values. |
|
|
DIVIDE |
Divides its values. Use: value1 / value2 Replace each value with fields or other numeric values. |
|
|
EXPONENTIATION |
Raises a number to the power of a specified number. Use: number^integer Replace number with a field or another numeric value. Replace integer with a field that contains an integer or any custom integer. |
|
|
OPEN & CLOSE PARENTHESES |
Specifies that the expressions within the open parenthesis and close parenthesis are evaluated first. All other expressions are evaluated from left to right. Use: (expression1) expression2... Replace each expression with fields or other numeric values. |
A summary formula cannot reference another summary formula.
The space for a custom formula contains up to 500 characters (including spaces and return characters).
Custom formulas can reference number, currency, and percent fields for the types of records and can contain fields of different data types.
When fields are deleted, they are also deleted from the summary formulas that reference them.
The Summary Types Sum, Largest Value, Smallest Value, and Average cannot be used with the Record Count field.
The Smallest Value Summary Type will include blank (null) or zero values in the formula calculation if they are present in your report data.
The Largest Value Summary Type will include the largest blank (non-null) value present in your report data.
Percents are represented as decimals in formulas. For example, 10% is represented as 0.10.
The Subtract Math Operator can be used to give fields in your custom formula a negative value. For example: {!INVOICES:COUNT} + -4.
"#Too Big!" will be displayed in your report if your custom formula output is over 18 digits. If this happens, check your formula for calculations that could result in more than 18 digits.
Avoid multiplying large numbers, raising a large number to a power, or dividing by a very small number.
Formulas treat blank (null) report cells as zero values.
"#Error!" displays on your report whenever an error occurs while calculating a formula's value or if your formula tries to divide by zero (0). To resolve the error, check the formula and provide an alternative value.
