﻿ Using Formulas to Create Calculations
 Using Formulas to Create Calculations

Performing Standard Data Operations > Creating Calculations > Using Formulas to Create Calculations

Calculated fields require formulas in order to actually create calculations.  This section will provide an overview of using formulas within calculated fields.

Formula Basics

A calculated field can be used to create calculations independent of any existing data.  For example, you may want to simply multiply two numbers together.  You could enter in the following formula:

20 * 5

This will produce a result of "100" for each record in your calculated field.  Similarly, you could add together two pieces of text such as:

"Ex" + "ample"

This will produce a result of "Example" for each record of your calculated field.  Or, you could create a Boolean expression (true or false logical condition):

3 > 1

This will produce a result of "T" for each record of your calculated field, since this formula is True.

Using Functions

Of course, calculated fields are far more powerful when you can add functions to perform more interesting tasks than simple mathematics.  For example, you may want to take a word and make it completely uppercase, such as:

upper("example")

This will produce a result of "EXAMPLE" for each record in your calculated field.  Or, if you wanted to find out the result of 3 to the 2nd power:

power(3,2)

This will produce a result of "9" for each record in your calculated field.

Referencing Existing Data

The examples above showed how you can create calculations based on fixed numbers and characters.  However, the true power of calculated fields become apparent when, instead of entering in fixed values, you actually reference values from other data fields.  For example, the following table shows a set of customers with the company name, invoice amount and discount terms:

 Company Amount Discount ABC INC 100 0.01 XYZ CORP 2000 0.02 ABC INC 400 0.05 RST LLC 50 0.05 XYZ CORP 1000 0.02 XYZ CORP 5000 0.02

I may want to create a calculated field that calculates the total discount they will receive:

Amount * Discount

 Company Amount Discount Calc_field ABC INC 100 0.01 1.00 XYZ CORP 2000 0.02 40.00 ABC INC 400 0.05 20.00 RST LLC 50 0.05 2.50 XYZ CORP 1000 0.02 20.00 XYZ CORP 5000 0.02 100.00

Or, I could use a function to provide me with specific information I am looking for.  Maybe in this case, discounts are not valid if they are less than \$1,000, so in order to only see valid discounts we would use this formula (using the IIF() function):

iif(Amount >= 1000, (Amount * Discount), 0)

 Company Amount Discount Calc_field ABC INC 100 0.01 0.00 XYZ CORP 2000 0.02 40.00 ABC INC 400 0.05 0.00 RST LLC 50 0.05 0.00 XYZ CORP 1000 0.02 20.00 XYZ CORP 5000 0.02 100.00

Referencing (Nesting) Calculations

Because calculated field act just like standard fields, they can be referenced in other calculated fields as well.  This can be quite helpful in building up calculations in a step by step manner by just creating new calculated fields each step of the way.  The alternative is to build the entire complicated formula in a single calculated field, which may prove more prone to error.  So, in the previous example, we had a calculated field called "Calc_field" that showed valid discounts.  If we wanted to determine our net receipts, we could create a second calculated field based upon that first calculated field:

Amount - Calc_field

 Company Amount Discount Calc_field Calc_field2 ABC INC 100 0.01 0.00 100.00 XYZ CORP 2000 0.02 40.00 1960.00 ABC INC 400 0.05 0.00 400.00 RST LLC 50 0.05 0.00 50.00 XYZ CORP 1000 0.02 20.00 980.00 XYZ CORP 5000 0.02 100.00 4900.00

Of course, there are always multiple ways to get to the correct result.  For example, we could have used any of these equivalent formulas in the beginning of this exercise to get the same result:

Amount - (iif(Amount >= 1000, (Amount * Discount), 0))

iif(Amount >= 1000, (Amount - (Amount * Discount)), Amount)

iif(Amount >= 1000, Amount * (1 - Discount), Amount)

Working with Formulas

Types of Formulas

Rules for Creating Formulas

Using the Formula Builder

Functions Used in Formulas