Calculation Fields

Calculation fields are very powerful feature that you can use to add an additional layer of intelligence to your Membrain experience. They are available on the Membrain Excellence Tier and can be used right across the board on Sales Projects, Prospects, Companies & Contacts.

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 want 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 @, 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:
  
  Making the Calculation Field...
   
  Show the calculated difference between the two Number Fields, like this:
  
• Calculate profit margin
  ((@Value - @OurCosts) / @Value) * 100
  
• Calculate revenue this fiscal year
  MonthsBetween(@ClosingDate, '2017-06-30') * @MonthlyValue
  

Considerations

Value Types

• Calculation fields works with a couple different types of values: Numbers, Numbers as Percent, Currencies and Dates.
• Dates can only be used as input to certain functions.
• Numbers can have up to 4 decimals. Numbers can come from a Number, Currency or Score Card Custom Field, or one of Membrains 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 Membrains 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. Sum is thus exactly 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
• 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
• Years Between
  Takes two dates and returns the number of months between those dates. Example Years Between('2016-01-20', '2018-02-12') = 2

Note that DaysBetween, MonthsBetween and YearsBetween REQUIRE you to supply both values. If either value is empty the entire calculation will fail and return no value.

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

The calculation fields are for performance reasons limited to a maximum calculation depth of 10 levels.