Calculation Fields

Calculation fields are a very powerful feature that you can use to add another layer of intelligence to your Membrain experience. 

They work with Number and Date properties, as well as Scorecard results, and can really give you lots of additional information by bringing data from different fields and presenting it as fresh information in a custom field. The great thing about calculation fields is that they are so flexible, and once you get started, they are quite easy to use.

How Calculation Fields Work

1. The end result of a Calculation Field is a number, percentage, or currency, presented in a Custom Field. So the first thing you need to do is create the Custom Fields you want to use in your calculation formula.
2. Next, you create the Calculation Field which will display your calculated information where you want it to be related to what information you would like to use.
3. From the Process Editor or Custom Field management area in System setup, create a new calculation Custom Field.
4. Use the Insert Property button to build your calculation. In this article, we will prefix any such variable with @, for example, @Value to make it clear that a variable is referenced.
   
   

Some Examples

• Subtract amounts in one custom field from another.
  In this example, Amount 1 and Amount 2 are predefined Custom Number Fields. We want to see Amount 1 minus Amount 2 in the new Calculation Field. This is achieved with the following setup:
  
  
  
  
• Calculate profit margin
  ((@Value - @OurCosts) / @Value) * 100
  
  
  
• Calculate revenue this fiscal year
  MonthsBetween(@ClosingDate, '2017-06-30') * @MonthlyValue
  

Considerations

Handling Missing Values in fields used in Calculations

Calculation Fields return an empty result when one or more fields referenced in the calculation do not contain a value. This helps ensure that calculations only produce results when all required data is available, preventing incomplete data from generating potentially misleading values.

If you want a Calculation Field to return a value even when some referenced fields are empty, you must wrap each field that could be empty with the Default() function. This ensures that a fallback value is used whenever a field does not contain data, allowing the calculation to continue.

The following are examples of the different scenarios you can use for a simple calculation where we want to take the Value of a Sales Project, and minus a custom field value which tracks "Costs" to get the total Profit. 

• Example 1: Return Empty Until All Data Is Available 

Use the Standard Calculation :
Value - Costs

If:
Value = €10,000
Costs = Empty

Result:
Empty

Because one of the fields used in the calculation is empty, the calculation result is also empty.

• Example 2 –  Continue Calculating Using Default Values 

Adjusted Calculation:
Default(Value, 0) - Default(Costs, 0)

If:
Value = €10,000
Costs = Empty

Result:
€10,000

Because Costs is wrapped in Default(), the empty value is replaced with 0, allowing the calculation to produce a result instead of returning empty.

Value Types

• Calculation fields work with a couple of different types of values: Numbers, Numbers as Percent, Currencies and Dates.
• Dates can only be used as input to certain functions. If you want to have a Date as the actual output, go check out our article on Date Calculation Fields
• Numbers can have up to 4 decimals. Numbers can come from a Number, Currency or Score Card Custom Field, or one of Membrain's base Number or Currency fields, such as Probability or Value.
• Dates are given as '2016-12-31'. Dates can come from a Date Custom Field, or one of Membrain's base Date properties, such as Closing Date.
  
  

Operators

Calculation fields support the following operators: +, -, * and /. Normal operator precedence applies, which means that * and / take precedence over + and -. Note that * and / have the same level of precedence and are processed from left to right. If you need to you can use parentheses to force a certain calculation order, ex: 3*(1+2) = 9 vs 3*1+2 = 5.

Functions

• Min
  Min takes any number of arguments, either a number or a referenced numerical property, and returns the smallest of those numbers. Example: Min(1,5,3) = 1
• Max
  Max takes any number of arguments, either a number or a referenced numerical property, and returns the largest of those numbers. Example: Max(1,5,3) = 5
• Sum
  Sum takes any number of arguments, either a number or a referenced numerical property, and returns the sum of those numbers. Example: Sum(1,5,3) = 9. The sum is thus the same as 1+5+3.
• Average
  Average gives you the average out of a series of numbers. Example: Average(1,2,3) = 2. You can also access properties; if the property is empty, it will be excluded from the average. Example: Average(1,@Empty,3) = 2.
• Default
  The default function can be used in cases where the value from a property may be empty, to resolve a default value. Example: Default(@Property, 5). In this case the value will be 5, if the Property is empty, otherwise it will use the value of the property.
• Round
  Rounds a number either up or down depending on its value. Example: Round(0.3) = 0. Round(0.6) = 1
• Floor
  Rounds a number down. Example: Floor(0.9) = 0. Floor(0.1) = 0
• Ceil
  Rounds a number up: Example: Ceil(0.1) =1. Ceil(0.8) = 1
• BusinessDaysBetween
  Calculates the number of business days between two dates, excluding weekends
• DaysBetween
  Takes two dates and returns the number of days between those dates. Example: DaysBetween('2016-01-01', '2016-02-01') = 31
• MonthsBetween
  Takes two dates and returns the number of months between those dates. Example: MonthsBetween('2016-01-20', '2016-04-12') = 3
• YearsBetween
  Takes two dates and returns the number of months between those dates. Example: YearsBetween('2016-01-20', '2018-02-12') = 2
  
  BusinessDaysBetween, DaysBetween, MonthsBetween and YearsBetween REQUIRE you to supply both values. If either value is empty, the calculation will fail and return no value.
  
  
• Nesting Functions
  Functions can be freely nested, so you can use the result from one function as input to another function. The one exception is the *Between functions, which cannot take another function or statement as input.

For example, you can create a calculation as follows:
Min(Floor(2.5), 3) = 2
or
Min(Max(3, 2+5), Max(5/2, 8)) = 3

• Calculation fields are, for performance reasons, limited to a maximum calculation depth of 10 levels.