Using Calculated Fields

Calculated Fields in Spiro give users the ability to write simple expressions that can perform calculations using data in Spiro. Calculated fields have built-in functions, including  if, not, min, max, sum, and round.

BUILT-IN OPERATORS AND FUNCTIONS 

Math: +, -, *, /, %, ^, |, &

Also, all functions from Ruby's Math module, including SIN, COS, TAN, etc.

Comparison: <, >, <=, >=, <>, !=, =,

Logic: IF, AND, OR, NOT, SWITCH

Numeric: MIN, MAX, SUM, AVG, COUNT, ROUND, ROUNDDOWN, ROUNDUP

Selections: CASE 

String: LEFT, RIGHT, MID, LEN, FIND, SUBSTITUTE, CONCAT, CONTAINS

DATE_DIFF(created_at, updated_at) 

DATE_DIFF(created_at,DATE_NOW())  [to get the exact number of days of opportunity since it was created]

CURRENCY(amount,'$') - it will return the defined amount of the opportunity and currency

DELIMINATED(amount) - should render just formatted number like 1,000,000.00 (edited)

MERGE FIELDS


The power of calculated fields is that you can merge fields from the record you are building them on so that they can change dynamically. Merge fields can be any field that exists in the record and will be listed for your reference when building the calculated field.

Merge field examples:
name 
amount 
user.email 
organization.name 
opportunity_stage.name 

You can also use custom fields as merge fields. 
----------

When creating calculated fields on the contact level, you may use the following out of the box merge fields from the company level in your formula: 
  • account_name  
  • account_address  
  • account_city 
  • account_zipcode  
  • account_country  
  • account_phone 
  • account_description  
  • account_state

Currently company level custom fields are not available when writing contact level formulas.

INVALID EXPRESSIONS

If you write a formula that causes argument error, the calculated field will display “Invalid Expression”.  Meaning, you have to go back into your field and write a valid expression that Spiro can understand.

EXAMPLES AND COMMON USE CASES


amount * 0.2 
amount * 10 / 100^2 
AVG(amount, 10) 
SUM(amount, tax_custom_field)
ROUND(amount % tax_custom_field) 
CONCAT(user.first_name, ' ', user.last_name) 
IF(amount < 10, 'Less than 10', 'More than 10') 
IF(CONTAINS('Won', opportunity_stage.name), 'WON', 'LOST') 
CASE opportunity_stage.name WHEN 'Closed won' THEN amount WHEN 'Qualifying' THEN .75*amount ELSE 0 END 

Common Use Case - Annual Revenue

Let's say you work at a subscription company. You may decide to have the opportunity amount field reflect the monthly revenue from an opportunity, but you also want to view the annual revenue as well. You would then create a calculated field called "Annual Revenue", and put the following in the calculation: 

amount * 12 

To make that value have a currency format, you could use this instead:

currency(amount*12,'$') 

Common Use Case - Weighted Pipeline

There are scenarios when you may want to reflect a separate amount value for an opportunity that changes based on the sales stage you are currently in. Create a new calculated field and call it "Weighted Pipeline" and enter the following in the calculation: 

currency(CASE opportunity_stage.name WHEN 'Prospecting' THEN amount *.1 WHEN 'Qualifying' THEN amount * .25 WHEN 'Proposing'  THEN amount * .5 WHEN 'Closed Won' THEN amount ELSE 0 END, '$')

What this calculated field is doing is giving the Weighted Pipeline less value based on the earlier sales stage. You may sub out your own sales stages and your own weighted calculations based on your own sales process.